| 1 |
<!-- doc/src/sgml/pltcl.sgml --> |
| 2 |
|
| 3 |
<chapter id="pltcl"> |
| 4 |
<title>PL/Tcl — Tcl Procedural Language</title> |
| 5 |
|
| 6 |
<indexterm zone="pltcl"> |
| 7 |
<primary>PL/Tcl</primary> |
| 8 |
</indexterm> |
| 9 |
|
| 10 |
<indexterm zone="pltcl"> |
| 11 |
<primary>Tcl</primary> |
| 12 |
</indexterm> |
| 13 |
|
| 14 |
<para> |
| 15 |
PL/Tcl is a loadable procedural language for the |
| 16 |
<productname>PostgreSQL</productname> database system |
| 17 |
that enables the <ulink url="https://www.tcl.tk/"> |
| 18 |
Tcl language</ulink> to be used to write |
| 19 |
<productname>PostgreSQL</productname> functions and procedures. |
| 20 |
</para> |
| 21 |
|
| 22 |
<!-- **** PL/Tcl overview **** --> |
| 23 |
|
| 24 |
<sect1 id="pltcl-overview"> |
| 25 |
<title>Overview</title> |
| 26 |
|
| 27 |
<para> |
| 28 |
PL/Tcl offers most of the capabilities a function writer has in |
| 29 |
the C language, with a few restrictions, and with the addition of |
| 30 |
the powerful string processing libraries that are available for |
| 31 |
Tcl. |
| 32 |
</para> |
| 33 |
<para> |
| 34 |
One compelling <emphasis>good</emphasis> restriction is that |
| 35 |
everything is executed from within the safety of the context of a |
| 36 |
Tcl interpreter. In addition to the limited command set of safe |
| 37 |
Tcl, only a few commands are available to access the database via |
| 38 |
SPI and to raise messages via <function>elog()</function>. PL/Tcl |
| 39 |
provides no way to access internals of the database server or to |
| 40 |
gain OS-level access under the permissions of the |
| 41 |
<productname>PostgreSQL</productname> server process, as a C |
| 42 |
function can do. Thus, unprivileged database users can be trusted |
| 43 |
to use this language; it does not give them unlimited authority. |
| 44 |
</para> |
| 45 |
<para> |
| 46 |
The other notable implementation restriction is that Tcl functions |
| 47 |
cannot be used to create input/output functions for new data |
| 48 |
types. |
| 49 |
</para> |
| 50 |
<para> |
| 51 |
Sometimes it is desirable to write Tcl functions that are not restricted |
| 52 |
to safe Tcl. For example, one might want a Tcl function that sends |
| 53 |
email. To handle these cases, there is a variant of <application>PL/Tcl</application> called <literal>PL/TclU</literal> |
| 54 |
(for untrusted Tcl). This is exactly the same language except that a full |
| 55 |
Tcl interpreter is used. <emphasis>If <application>PL/TclU</application> is used, it must be |
| 56 |
installed as an untrusted procedural language</emphasis> so that only |
| 57 |
database superusers can create functions in it. The writer of a <application>PL/TclU</application> |
| 58 |
function must take care that the function cannot be used to do anything |
| 59 |
unwanted, since it will be able to do anything that could be done by |
| 60 |
a user logged in as the database administrator. |
| 61 |
</para> |
| 62 |
<para> |
| 63 |
The shared object code for the <application>PL/Tcl</application> and |
| 64 |
<application>PL/TclU</application> call handlers is automatically built and |
| 65 |
installed in the <productname>PostgreSQL</productname> library |
| 66 |
directory if Tcl support is specified in the configuration step of |
| 67 |
the installation procedure. To install <application>PL/Tcl</application> |
| 68 |
and/or <application>PL/TclU</application> in a particular database, use the |
| 69 |
<command>CREATE EXTENSION</command> command, for example |
| 70 |
<literal>CREATE EXTENSION pltcl</literal> or |
| 71 |
<literal>CREATE EXTENSION pltclu</literal>. |
| 72 |
</para> |
| 73 |
</sect1> |
| 74 |
|
| 75 |
<!-- **** PL/Tcl description **** --> |
| 76 |
|
| 77 |
<sect1 id="pltcl-functions"> |
| 78 |
<title>PL/Tcl Functions and Arguments</title> |
| 79 |
|
| 80 |
<para> |
| 81 |
To create a function in the <application>PL/Tcl</application> language, use |
| 82 |
the standard <xref linkend="sql-createfunction"/> syntax: |
| 83 |
|
| 84 |
<programlisting> |
| 85 |
CREATE FUNCTION <replaceable>funcname</replaceable> (<replaceable>argument-types</replaceable>) RETURNS <replaceable>return-type</replaceable> AS $$ |
| 86 |
# PL/Tcl function body |
| 87 |
$$ LANGUAGE pltcl; |
| 88 |
</programlisting> |
| 89 |
|
| 90 |
<application>PL/TclU</application> is the same, except that the language has to be specified as |
| 91 |
<literal>pltclu</literal>. |
| 92 |
</para> |
| 93 |
|
| 94 |
<para> |
| 95 |
The body of the function is simply a piece of Tcl script. |
| 96 |
When the function is called, the argument values are passed to the |
| 97 |
Tcl script as variables named <literal>1</literal> |
| 98 |
... <literal><replaceable>n</replaceable></literal>. The result is |
| 99 |
returned from the Tcl code in the usual way, with |
| 100 |
a <literal>return</literal> statement. In a procedure, the return value |
| 101 |
from the Tcl code is ignored. |
| 102 |
</para> |
| 103 |
|
| 104 |
<para> |
| 105 |
For example, a function |
| 106 |
returning the greater of two integer values could be defined as: |
| 107 |
|
| 108 |
<programlisting> |
| 109 |
CREATE FUNCTION tcl_max(integer, integer) RETURNS integer AS $$ |
| 110 |
if {$1 > $2} {return $1} |
| 111 |
return $2 |
| 112 |
$$ LANGUAGE pltcl STRICT; |
| 113 |
</programlisting> |
| 114 |
|
| 115 |
Note the clause <literal>STRICT</literal>, which saves us from |
| 116 |
having to think about null input values: if a null value is passed, the |
| 117 |
function will not be called at all, but will just return a null |
| 118 |
result automatically. |
| 119 |
</para> |
| 120 |
|
| 121 |
<para> |
| 122 |
In a nonstrict function, |
| 123 |
if the actual value of an argument is null, the corresponding |
| 124 |
<literal>$<replaceable>n</replaceable></literal> variable will be set to an empty string. |
| 125 |
To detect whether a particular argument is null, use the function |
| 126 |
<literal>argisnull</literal>. For example, suppose that we wanted <function>tcl_max</function> |
| 127 |
with one null and one nonnull argument to return the nonnull |
| 128 |
argument, rather than null: |
| 129 |
|
| 130 |
<programlisting> |
| 131 |
CREATE FUNCTION tcl_max(integer, integer) RETURNS integer AS $$ |
| 132 |
if {[argisnull 1]} { |
| 133 |
if {[argisnull 2]} { return_null } |
| 134 |
return $2 |
| 135 |
} |
| 136 |
if {[argisnull 2]} { return $1 } |
| 137 |
if {$1 > $2} {return $1} |
| 138 |
return $2 |
| 139 |
$$ LANGUAGE pltcl; |
| 140 |
</programlisting> |
| 141 |
</para> |
| 142 |
|
| 143 |
<para> |
| 144 |
As shown above, |
| 145 |
to return a null value from a PL/Tcl function, execute |
| 146 |
<literal>return_null</literal>. This can be done whether the |
| 147 |
function is strict or not. |
| 148 |
</para> |
| 149 |
|
| 150 |
<para> |
| 151 |
Composite-type arguments are passed to the function as Tcl |
| 152 |
arrays. The element names of the array are the attribute names |
| 153 |
of the composite type. If an attribute in the passed row has the |
| 154 |
null value, it will not appear in the array. Here is an example: |
| 155 |
|
| 156 |
<programlisting> |
| 157 |
CREATE TABLE employee ( |
| 158 |
name text, |
| 159 |
salary integer, |
| 160 |
age integer |
| 161 |
); |
| 162 |
|
| 163 |
CREATE FUNCTION overpaid(employee) RETURNS boolean AS $$ |
| 164 |
if {200000.0 < $1(salary)} { |
| 165 |
return "t" |
| 166 |
} |
| 167 |
if {$1(age) < 30 && 100000.0 < $1(salary)} { |
| 168 |
return "t" |
| 169 |
} |
| 170 |
return "f" |
| 171 |
$$ LANGUAGE pltcl; |
| 172 |
</programlisting> |
| 173 |
</para> |
| 174 |
|
| 175 |
<para> |
| 176 |
PL/Tcl functions can return composite-type results, too. To do this, |
| 177 |
the Tcl code must return a list of column name/value pairs matching |
| 178 |
the expected result type. Any column names omitted from the list |
| 179 |
are returned as nulls, and an error is raised if there are unexpected |
| 180 |
column names. Here is an example: |
| 181 |
|
| 182 |
<programlisting> |
| 183 |
CREATE FUNCTION square_cube(in int, out squared int, out cubed int) AS $$ |
| 184 |
return [list squared [expr {$1 * $1}] cubed [expr {$1 * $1 * $1}]] |
| 185 |
$$ LANGUAGE pltcl; |
| 186 |
</programlisting> |
| 187 |
</para> |
| 188 |
|
| 189 |
<para> |
| 190 |
Output arguments of procedures are returned in the same way, for example: |
| 191 |
|
| 192 |
<programlisting> |
| 193 |
CREATE PROCEDURE tcl_triple(INOUT a integer, INOUT b integer) AS $$ |
| 194 |
return [list a [expr {$1 * 3}] b [expr {$2 * 3}]] |
| 195 |
$$ LANGUAGE pltcl; |
| 196 |
|
| 197 |
CALL tcl_triple(5, 10); |
| 198 |
</programlisting> |
| 199 |
</para> |
| 200 |
|
| 201 |
<tip> |
| 202 |
<para> |
| 203 |
The result list can be made from an array representation of the |
| 204 |
desired tuple with the <literal>array get</literal> Tcl command. For example: |
| 205 |
|
| 206 |
<programlisting> |
| 207 |
CREATE FUNCTION raise_pay(employee, delta int) RETURNS employee AS $$ |
| 208 |
set 1(salary) [expr {$1(salary) + $2}] |
| 209 |
return [array get 1] |
| 210 |
$$ LANGUAGE pltcl; |
| 211 |
</programlisting> |
| 212 |
</para> |
| 213 |
</tip> |
| 214 |
|
| 215 |
<para> |
| 216 |
PL/Tcl functions can return sets. To do this, the Tcl code should |
| 217 |
call <function>return_next</function> once per row to be returned, |
| 218 |
passing either the appropriate value when returning a scalar type, |
| 219 |
or a list of column name/value pairs when returning a composite type. |
| 220 |
Here is an example returning a scalar type: |
| 221 |
|
| 222 |
<programlisting> |
| 223 |
CREATE FUNCTION sequence(int, int) RETURNS SETOF int AS $$ |
| 224 |
for {set i $1} {$i < $2} {incr i} { |
| 225 |
return_next $i |
| 226 |
} |
| 227 |
$$ LANGUAGE pltcl; |
| 228 |
</programlisting> |
| 229 |
|
| 230 |
and here is one returning a composite type: |
| 231 |
|
| 232 |
<programlisting> |
| 233 |
CREATE FUNCTION table_of_squares(int, int) RETURNS TABLE (x int, x2 int) AS $$ |
| 234 |
for {set i $1} {$i < $2} {incr i} { |
| 235 |
return_next [list x $i x2 [expr {$i * $i}]] |
| 236 |
} |
| 237 |
$$ LANGUAGE pltcl; |
| 238 |
</programlisting> |
| 239 |
</para> |
| 240 |
|
| 241 |
</sect1> |
| 242 |
|
| 243 |
<sect1 id="pltcl-data"> |
| 244 |
<title>Data Values in PL/Tcl</title> |
| 245 |
|
| 246 |
<para> |
| 247 |
The argument values supplied to a PL/Tcl function's code are simply |
| 248 |
the input arguments converted to text form (just as if they had been |
| 249 |
displayed by a <command>SELECT</command> statement). Conversely, the |
| 250 |
<literal>return</literal> and <literal>return_next</literal> commands will accept |
| 251 |
any string that is acceptable input format for the function's declared |
| 252 |
result type, or for the specified column of a composite result type. |
| 253 |
</para> |
| 254 |
|
| 255 |
</sect1> |
| 256 |
|
| 257 |
<sect1 id="pltcl-global"> |
| 258 |
<title>Global Data in PL/Tcl</title> |
| 259 |
|
| 260 |
<indexterm zone="pltcl-global"> |
| 261 |
<primary>global data</primary> |
| 262 |
<secondary>in PL/Tcl</secondary> |
| 263 |
</indexterm> |
| 264 |
|
| 265 |
<para> |
| 266 |
Sometimes it |
| 267 |
is useful to have some global data that is held between two |
| 268 |
calls to a function or is shared between different functions. |
| 269 |
This is easily done in PL/Tcl, but there are some restrictions that |
| 270 |
must be understood. |
| 271 |
</para> |
| 272 |
|
| 273 |
<para> |
| 274 |
For security reasons, PL/Tcl executes functions called by any one SQL |
| 275 |
role in a separate Tcl interpreter for that role. This prevents |
| 276 |
accidental or malicious interference by one user with the behavior of |
| 277 |
another user's PL/Tcl functions. Each such interpreter will have its own |
| 278 |
values for any <quote>global</quote> Tcl variables. Thus, two PL/Tcl |
| 279 |
functions will share the same global variables if and only if they are |
| 280 |
executed by the same SQL role. In an application wherein a single |
| 281 |
session executes code under multiple SQL roles (via <literal>SECURITY |
| 282 |
DEFINER</literal> functions, use of <command>SET ROLE</command>, etc.) you may need to |
| 283 |
take explicit steps to ensure that PL/Tcl functions can share data. To |
| 284 |
do that, make sure that functions that should communicate are owned by |
| 285 |
the same user, and mark them <literal>SECURITY DEFINER</literal>. You must of |
| 286 |
course take care that such functions can't be used to do anything |
| 287 |
unintended. |
| 288 |
</para> |
| 289 |
|
| 290 |
<para> |
| 291 |
All PL/TclU functions used in a session execute in the same Tcl |
| 292 |
interpreter, which of course is distinct from the interpreter(s) |
| 293 |
used for PL/Tcl functions. So global data is automatically shared |
| 294 |
between PL/TclU functions. This is not considered a security risk |
| 295 |
because all PL/TclU functions execute at the same trust level, |
| 296 |
namely that of a database superuser. |
| 297 |
</para> |
| 298 |
|
| 299 |
<para> |
| 300 |
To help protect PL/Tcl functions from unintentionally interfering |
| 301 |
with each other, a global |
| 302 |
array is made available to each function via the <function>upvar</function> |
| 303 |
command. The global name of this variable is the function's internal |
| 304 |
name, and the local name is <literal>GD</literal>. It is recommended that |
| 305 |
<literal>GD</literal> be used |
| 306 |
for persistent private data of a function. Use regular Tcl global |
| 307 |
variables only for values that you specifically intend to be shared among |
| 308 |
multiple functions. (Note that the <literal>GD</literal> arrays are only |
| 309 |
global within a particular interpreter, so they do not bypass the |
| 310 |
security restrictions mentioned above.) |
| 311 |
</para> |
| 312 |
|
| 313 |
<para> |
| 314 |
An example of using <literal>GD</literal> appears in the |
| 315 |
<function>spi_execp</function> example below. |
| 316 |
</para> |
| 317 |
</sect1> |
| 318 |
|
| 319 |
<sect1 id="pltcl-dbaccess"> |
| 320 |
<title>Database Access from PL/Tcl</title> |
| 321 |
|
| 322 |
<para> |
| 323 |
In this section, we follow the usual Tcl convention of using question |
| 324 |
marks, rather than brackets, to indicate an optional element in a |
| 325 |
syntax synopsis. The following commands are available to access |
| 326 |
the database from the body of a PL/Tcl function: |
| 327 |
|
| 328 |
<variablelist> |
| 329 |
|
| 330 |
<varlistentry> |
| 331 |
<term><literal><function>spi_exec</function> <optional role="tcl">-count <replaceable>n</replaceable></optional> <optional role="tcl">-array <replaceable>name</replaceable></optional> <replaceable>command</replaceable> <optional role="tcl"><replaceable>loop-body</replaceable></optional></literal></term> |
| 332 |
<listitem> |
| 333 |
<para> |
| 334 |
Executes an SQL command given as a string. An error in the command |
| 335 |
causes an error to be raised. Otherwise, the return value of <function>spi_exec</function> |
| 336 |
is the number of rows processed (selected, inserted, updated, or |
| 337 |
deleted) by the command, or zero if the command is a utility |
| 338 |
statement. In addition, if the command is a <command>SELECT</command> statement, the |
| 339 |
values of the selected columns are placed in Tcl variables as |
| 340 |
described below. |
| 341 |
</para> |
| 342 |
<para> |
| 343 |
The optional <literal>-count</literal> value tells |
| 344 |
<function>spi_exec</function> to stop |
| 345 |
once <replaceable>n</replaceable> rows have been retrieved, |
| 346 |
much as if the query included a <literal>LIMIT</literal> clause. |
| 347 |
If <replaceable>n</replaceable> is zero, the query is run to |
| 348 |
completion, the same as when <literal>-count</literal> is omitted. |
| 349 |
</para> |
| 350 |
<para> |
| 351 |
If the command is a <command>SELECT</command> statement, the values of the |
| 352 |
result columns are placed into Tcl variables named after the columns. |
| 353 |
If the <literal>-array</literal> option is given, the column values are |
| 354 |
instead stored into elements of the named associative array, with the |
| 355 |
column names used as array indexes. In addition, the current row |
| 356 |
number within the result (counting from zero) is stored into the array |
| 357 |
element named <quote><literal>.tupno</literal></quote>, unless that name is |
| 358 |
in use as a column name in the result. |
| 359 |
</para> |
| 360 |
<para> |
| 361 |
If the command is a <command>SELECT</command> statement and no <replaceable>loop-body</replaceable> |
| 362 |
script is given, then only the first row of results are stored into |
| 363 |
Tcl variables or array elements; remaining rows, if any, are ignored. |
| 364 |
No storing occurs if the query returns no rows. (This case can be |
| 365 |
detected by checking the result of <function>spi_exec</function>.) |
| 366 |
For example: |
| 367 |
<programlisting> |
| 368 |
spi_exec "SELECT count(*) AS cnt FROM pg_proc" |
| 369 |
</programlisting> |
| 370 |
will set the Tcl variable <literal>$cnt</literal> to the number of rows in |
| 371 |
the <structname>pg_proc</structname> system catalog. |
| 372 |
</para> |
| 373 |
<para> |
| 374 |
If the optional <replaceable>loop-body</replaceable> argument is given, it is |
| 375 |
a piece of Tcl script that is executed once for each row in the |
| 376 |
query result. (<replaceable>loop-body</replaceable> is ignored if the given |
| 377 |
command is not a <command>SELECT</command>.) |
| 378 |
The values of the current row's columns |
| 379 |
are stored into Tcl variables or array elements before each iteration. |
| 380 |
For example: |
| 381 |
<programlisting> |
| 382 |
spi_exec -array C "SELECT * FROM pg_class" { |
| 383 |
elog DEBUG "have table $C(relname)" |
| 384 |
} |
| 385 |
</programlisting> |
| 386 |
will print a log message for every row of <literal>pg_class</literal>. This |
| 387 |
feature works similarly to other Tcl looping constructs; in |
| 388 |
particular <literal>continue</literal> and <literal>break</literal> work in the |
| 389 |
usual way inside the loop body. |
| 390 |
</para> |
| 391 |
<para> |
| 392 |
If a column of a query result is null, the target |
| 393 |
variable for it is <quote>unset</quote> rather than being set. |
| 394 |
</para> |
| 395 |
</listitem> |
| 396 |
</varlistentry> |
| 397 |
|
| 398 |
<varlistentry> |
| 399 |
<term><function>spi_prepare</function> <replaceable>query</replaceable> <replaceable>typelist</replaceable></term> |
| 400 |
<listitem> |
| 401 |
<para> |
| 402 |
Prepares and saves a query plan for later execution. The |
| 403 |
saved plan will be retained for the life of the current |
| 404 |
session.<indexterm><primary>preparing a query</primary> |
| 405 |
<secondary>in PL/Tcl</secondary></indexterm> |
| 406 |
</para> |
| 407 |
<para> |
| 408 |
The query can use parameters, that is, placeholders for |
| 409 |
values to be supplied whenever the plan is actually executed. |
| 410 |
In the query string, refer to parameters |
| 411 |
by the symbols <literal>$1</literal> ... <literal>$<replaceable>n</replaceable></literal>. |
| 412 |
If the query uses parameters, the names of the parameter types |
| 413 |
must be given as a Tcl list. (Write an empty list for |
| 414 |
<replaceable>typelist</replaceable> if no parameters are used.) |
| 415 |
</para> |
| 416 |
<para> |
| 417 |
The return value from <function>spi_prepare</function> is a query ID |
| 418 |
to be used in subsequent calls to <function>spi_execp</function>. See |
| 419 |
<function>spi_execp</function> for an example. |
| 420 |
</para> |
| 421 |
</listitem> |
| 422 |
</varlistentry> |
| 423 |
|
| 424 |
<varlistentry> |
| 425 |
<term><literal><function>spi_execp</function> <optional role="tcl">-count <replaceable>n</replaceable></optional> <optional role="tcl">-array <replaceable>name</replaceable></optional> <optional role="tcl">-nulls <replaceable>string</replaceable></optional> <replaceable>queryid</replaceable> <optional role="tcl"><replaceable>value-list</replaceable></optional> <optional role="tcl"><replaceable>loop-body</replaceable></optional></literal></term> |
| 426 |
<listitem> |
| 427 |
<para> |
| 428 |
Executes a query previously prepared with <function>spi_prepare</function>. |
| 429 |
<replaceable>queryid</replaceable> is the ID returned by |
| 430 |
<function>spi_prepare</function>. If the query references parameters, |
| 431 |
a <replaceable>value-list</replaceable> must be supplied. This |
| 432 |
is a Tcl list of actual values for the parameters. The list must be |
| 433 |
the same length as the parameter type list previously given to |
| 434 |
<function>spi_prepare</function>. Omit <replaceable>value-list</replaceable> |
| 435 |
if the query has no parameters. |
| 436 |
</para> |
| 437 |
<para> |
| 438 |
The optional value for <literal>-nulls</literal> is a string of spaces and |
| 439 |
<literal>'n'</literal> characters telling <function>spi_execp</function> |
| 440 |
which of the parameters are null values. If given, it must have exactly the |
| 441 |
same length as the <replaceable>value-list</replaceable>. If it |
| 442 |
is not given, all the parameter values are nonnull. |
| 443 |
</para> |
| 444 |
<para> |
| 445 |
Except for the way in which the query and its parameters are specified, |
| 446 |
<function>spi_execp</function> works just like <function>spi_exec</function>. |
| 447 |
The <literal>-count</literal>, <literal>-array</literal>, and |
| 448 |
<replaceable>loop-body</replaceable> options are the same, |
| 449 |
and so is the result value. |
| 450 |
</para> |
| 451 |
<para> |
| 452 |
Here's an example of a PL/Tcl function using a prepared plan: |
| 453 |
|
| 454 |
<programlisting> |
| 455 |
CREATE FUNCTION t1_count(integer, integer) RETURNS integer AS $$ |
| 456 |
if {![ info exists GD(plan) ]} { |
| 457 |
# prepare the saved plan on the first call |
| 458 |
set GD(plan) [ spi_prepare \ |
| 459 |
"SELECT count(*) AS cnt FROM t1 WHERE num >= \$1 AND num <= \$2" \ |
| 460 |
[ list int4 int4 ] ] |
| 461 |
} |
| 462 |
spi_execp -count 1 $GD(plan) [ list $1 $2 ] |
| 463 |
return $cnt |
| 464 |
$$ LANGUAGE pltcl; |
| 465 |
</programlisting> |
| 466 |
|
| 467 |
We need backslashes inside the query string given to |
| 468 |
<function>spi_prepare</function> to ensure that the |
| 469 |
<literal>$<replaceable>n</replaceable></literal> markers will be passed |
| 470 |
through to <function>spi_prepare</function> as-is, and not replaced by Tcl |
| 471 |
variable substitution. |
| 472 |
|
| 473 |
</para> |
| 474 |
</listitem> |
| 475 |
</varlistentry> |
| 476 |
|
| 477 |
<varlistentry> |
| 478 |
<term><function>subtransaction</function> <replaceable>command</replaceable></term> |
| 479 |
<listitem> |
| 480 |
<para> |
| 481 |
The Tcl script contained in <replaceable>command</replaceable> is |
| 482 |
executed within an SQL subtransaction. If the script returns an |
| 483 |
error, that entire subtransaction is rolled back before returning the |
| 484 |
error out to the surrounding Tcl code. |
| 485 |
See <xref linkend="pltcl-subtransactions"/> for more details and an |
| 486 |
example. |
| 487 |
</para> |
| 488 |
</listitem> |
| 489 |
</varlistentry> |
| 490 |
|
| 491 |
<varlistentry> |
| 492 |
<term><function>quote</function> <replaceable>string</replaceable></term> |
| 493 |
<listitem> |
| 494 |
<para> |
| 495 |
Doubles all occurrences of single quote and backslash characters |
| 496 |
in the given string. This can be used to safely quote strings |
| 497 |
that are to be inserted into SQL commands given |
| 498 |
to <function>spi_exec</function> or |
| 499 |
<function>spi_prepare</function>. |
| 500 |
For example, think about an SQL command string like: |
| 501 |
|
| 502 |
<programlisting> |
| 503 |
"SELECT '$val' AS ret" |
| 504 |
</programlisting> |
| 505 |
|
| 506 |
where the Tcl variable <literal>val</literal> actually contains |
| 507 |
<literal>doesn't</literal>. This would result |
| 508 |
in the final command string: |
| 509 |
|
| 510 |
<programlisting> |
| 511 |
SELECT 'doesn't' AS ret |
| 512 |
</programlisting> |
| 513 |
|
| 514 |
which would cause a parse error during |
| 515 |
<function>spi_exec</function> or |
| 516 |
<function>spi_prepare</function>. |
| 517 |
To work properly, the submitted command should contain: |
| 518 |
|
| 519 |
<programlisting> |
| 520 |
SELECT 'doesn''t' AS ret |
| 521 |
</programlisting> |
| 522 |
|
| 523 |
which can be formed in PL/Tcl using: |
| 524 |
|
| 525 |
<programlisting> |
| 526 |
"SELECT '[ quote $val ]' AS ret" |
| 527 |
</programlisting> |
| 528 |
|
| 529 |
One advantage of <function>spi_execp</function> is that you don't |
| 530 |
have to quote parameter values like this, since the parameters are never |
| 531 |
parsed as part of an SQL command string. |
| 532 |
</para> |
| 533 |
</listitem> |
| 534 |
</varlistentry> |
| 535 |
|
| 536 |
<varlistentry> |
| 537 |
<term> |
| 538 |
<function>elog</function> <replaceable>level</replaceable> <replaceable>msg</replaceable> |
| 539 |
<indexterm> |
| 540 |
<primary>elog</primary> |
| 541 |
<secondary>in PL/Tcl</secondary> |
| 542 |
</indexterm> |
| 543 |
</term> |
| 544 |
<listitem> |
| 545 |
<para> |
| 546 |
Emits a log or error message. Possible levels are |
| 547 |
<literal>DEBUG</literal>, <literal>LOG</literal>, <literal>INFO</literal>, |
| 548 |
<literal>NOTICE</literal>, <literal>WARNING</literal>, <literal>ERROR</literal>, and |
| 549 |
<literal>FATAL</literal>. <literal>ERROR</literal> |
| 550 |
raises an error condition; if this is not trapped by the surrounding |
| 551 |
Tcl code, the error propagates out to the calling query, causing |
| 552 |
the current transaction or subtransaction to be aborted. This |
| 553 |
is effectively the same as the Tcl <literal>error</literal> command. |
| 554 |
<literal>FATAL</literal> aborts the transaction and causes the current |
| 555 |
session to shut down. (There is probably no good reason to use |
| 556 |
this error level in PL/Tcl functions, but it's provided for |
| 557 |
completeness.) The other levels only generate messages of different |
| 558 |
priority levels. |
| 559 |
Whether messages of a particular priority are reported to the client, |
| 560 |
written to the server log, or both is controlled by the |
| 561 |
<xref linkend="guc-log-min-messages"/> and |
| 562 |
<xref linkend="guc-client-min-messages"/> configuration |
| 563 |
variables. See <xref linkend="runtime-config"/> |
| 564 |
and <xref linkend="pltcl-error-handling"/> |
| 565 |
for more information. |
| 566 |
</para> |
| 567 |
</listitem> |
| 568 |
</varlistentry> |
| 569 |
|
| 570 |
</variablelist> |
| 571 |
</para> |
| 572 |
|
| 573 |
</sect1> |
| 574 |
|
| 575 |
<sect1 id="pltcl-trigger"> |
| 576 |
<title>Trigger Functions in PL/Tcl</title> |
| 577 |
|
| 578 |
<indexterm> |
| 579 |
<primary>trigger</primary> |
| 580 |
<secondary>in PL/Tcl</secondary> |
| 581 |
</indexterm> |
| 582 |
|
| 583 |
<para> |
| 584 |
Trigger functions can be written in PL/Tcl. |
| 585 |
<productname>PostgreSQL</productname> requires that a function that is to be called |
| 586 |
as a trigger must be declared as a function with no arguments |
| 587 |
and a return type of <literal>trigger</literal>. |
| 588 |
</para> |
| 589 |
<para> |
| 590 |
The information from the trigger manager is passed to the function body |
| 591 |
in the following variables: |
| 592 |
|
| 593 |
<variablelist> |
| 594 |
|
| 595 |
<varlistentry> |
| 596 |
<term><varname>$TG_name</varname></term> |
| 597 |
<listitem> |
| 598 |
<para> |
| 599 |
The name of the trigger from the <command>CREATE TRIGGER</command> statement. |
| 600 |
</para> |
| 601 |
</listitem> |
| 602 |
</varlistentry> |
| 603 |
|
| 604 |
<varlistentry> |
| 605 |
<term><varname>$TG_relid</varname></term> |
| 606 |
<listitem> |
| 607 |
<para> |
| 608 |
The object ID of the table that caused the trigger function |
| 609 |
to be invoked. |
| 610 |
</para> |
| 611 |
</listitem> |
| 612 |
</varlistentry> |
| 613 |
|
| 614 |
<varlistentry> |
| 615 |
<term><varname>$TG_table_name</varname></term> |
| 616 |
<listitem> |
| 617 |
<para> |
| 618 |
The name of the table that caused the trigger function |
| 619 |
to be invoked. |
| 620 |
</para> |
| 621 |
</listitem> |
| 622 |
</varlistentry> |
| 623 |
|
| 624 |
<varlistentry> |
| 625 |
<term><varname>$TG_table_schema</varname></term> |
| 626 |
<listitem> |
| 627 |
<para> |
| 628 |
The schema of the table that caused the trigger function |
| 629 |
to be invoked. |
| 630 |
</para> |
| 631 |
</listitem> |
| 632 |
</varlistentry> |
| 633 |
|
| 634 |
<varlistentry> |
| 635 |
<term><varname>$TG_relatts</varname></term> |
| 636 |
<listitem> |
| 637 |
<para> |
| 638 |
A Tcl list of the table column names, prefixed with an empty list |
| 639 |
element. So looking up a column name in the list with <application>Tcl</application>'s |
| 640 |
<function>lsearch</function> command returns the element's number starting |
| 641 |
with 1 for the first column, the same way the columns are customarily |
| 642 |
numbered in <productname>PostgreSQL</productname>. (Empty list |
| 643 |
elements also appear in the positions of columns that have been |
| 644 |
dropped, so that the attribute numbering is correct for columns |
| 645 |
to their right.) |
| 646 |
</para> |
| 647 |
</listitem> |
| 648 |
</varlistentry> |
| 649 |
|
| 650 |
<varlistentry> |
| 651 |
<term><varname>$TG_when</varname></term> |
| 652 |
<listitem> |
| 653 |
<para> |
| 654 |
The string <literal>BEFORE</literal>, <literal>AFTER</literal>, or |
| 655 |
<literal>INSTEAD OF</literal>, depending on the type of trigger event. |
| 656 |
</para> |
| 657 |
</listitem> |
| 658 |
</varlistentry> |
| 659 |
|
| 660 |
<varlistentry> |
| 661 |
<term><varname>$TG_level</varname></term> |
| 662 |
<listitem> |
| 663 |
<para> |
| 664 |
The string <literal>ROW</literal> or <literal>STATEMENT</literal> depending on the |
| 665 |
type of trigger event. |
| 666 |
</para> |
| 667 |
</listitem> |
| 668 |
</varlistentry> |
| 669 |
|
| 670 |
<varlistentry> |
| 671 |
<term><varname>$TG_op</varname></term> |
| 672 |
<listitem> |
| 673 |
<para> |
| 674 |
The string <literal>INSERT</literal>, <literal>UPDATE</literal>, |
| 675 |
<literal>DELETE</literal>, or <literal>TRUNCATE</literal> depending on the type of |
| 676 |
trigger event. |
| 677 |
</para> |
| 678 |
</listitem> |
| 679 |
</varlistentry> |
| 680 |
|
| 681 |
<varlistentry> |
| 682 |
<term><varname>$NEW</varname></term> |
| 683 |
<listitem> |
| 684 |
<para> |
| 685 |
An associative array containing the values of the new table |
| 686 |
row for <command>INSERT</command> or <command>UPDATE</command> actions, or |
| 687 |
empty for <command>DELETE</command>. The array is indexed by column |
| 688 |
name. Columns that are null will not appear in the array. |
| 689 |
This is not set for statement-level triggers. |
| 690 |
</para> |
| 691 |
</listitem> |
| 692 |
</varlistentry> |
| 693 |
|
| 694 |
<varlistentry> |
| 695 |
<term><varname>$OLD</varname></term> |
| 696 |
<listitem> |
| 697 |
<para> |
| 698 |
An associative array containing the values of the old table |
| 699 |
row for <command>UPDATE</command> or <command>DELETE</command> actions, or |
| 700 |
empty for <command>INSERT</command>. The array is indexed by column |
| 701 |
name. Columns that are null will not appear in the array. |
| 702 |
This is not set for statement-level triggers. |
| 703 |
</para> |
| 704 |
</listitem> |
| 705 |
</varlistentry> |
| 706 |
|
| 707 |
<varlistentry> |
| 708 |
<term><varname>$args</varname></term> |
| 709 |
<listitem> |
| 710 |
<para> |
| 711 |
A Tcl list of the arguments to the function as given in the |
| 712 |
<command>CREATE TRIGGER</command> statement. These arguments are also accessible as |
| 713 |
<literal>$1</literal> ... <literal>$<replaceable>n</replaceable></literal> in the function body. |
| 714 |
</para> |
| 715 |
</listitem> |
| 716 |
</varlistentry> |
| 717 |
|
| 718 |
</variablelist> |
| 719 |
</para> |
| 720 |
|
| 721 |
<para> |
| 722 |
The return value from a trigger function can be one of the strings |
| 723 |
<literal>OK</literal> or <literal>SKIP</literal>, or a list of column name/value pairs. |
| 724 |
If the return value is <literal>OK</literal>, |
| 725 |
the operation (<command>INSERT</command>/<command>UPDATE</command>/<command>DELETE</command>) |
| 726 |
that fired the trigger will proceed |
| 727 |
normally. <literal>SKIP</literal> tells the trigger manager to silently suppress |
| 728 |
the operation for this row. If a list is returned, it tells PL/Tcl to |
| 729 |
return a modified row to the trigger manager; the contents of the |
| 730 |
modified row are specified by the column names and values in the list. |
| 731 |
Any columns not mentioned in the list are set to null. |
| 732 |
Returning a modified row is only meaningful |
| 733 |
for row-level <literal>BEFORE</literal> <command>INSERT</command> or <command>UPDATE</command> |
| 734 |
triggers, for which the modified row will be inserted instead of the one |
| 735 |
given in <varname>$NEW</varname>; or for row-level <literal>INSTEAD OF</literal> |
| 736 |
<command>INSERT</command> or <command>UPDATE</command> triggers where the returned row |
| 737 |
is used as the source data for <command>INSERT RETURNING</command> or |
| 738 |
<command>UPDATE RETURNING</command> clauses. |
| 739 |
In row-level <literal>BEFORE</literal> <command>DELETE</command> or <literal>INSTEAD |
| 740 |
OF</literal> <command>DELETE</command> triggers, returning a modified row has the same |
| 741 |
effect as returning <literal>OK</literal>, that is the operation proceeds. |
| 742 |
The trigger return value is ignored for all other types of triggers. |
| 743 |
</para> |
| 744 |
|
| 745 |
<tip> |
| 746 |
<para> |
| 747 |
The result list can be made from an array representation of the |
| 748 |
modified tuple with the <literal>array get</literal> Tcl command. |
| 749 |
</para> |
| 750 |
</tip> |
| 751 |
|
| 752 |
<para> |
| 753 |
Here's a little example trigger function that forces an integer value |
| 754 |
in a table to keep track of the number of updates that are performed on the |
| 755 |
row. For new rows inserted, the value is initialized to 0 and then |
| 756 |
incremented on every update operation. |
| 757 |
|
| 758 |
<programlisting> |
| 759 |
CREATE FUNCTION trigfunc_modcount() RETURNS trigger AS $$ |
| 760 |
switch $TG_op { |
| 761 |
INSERT { |
| 762 |
set NEW($1) 0 |
| 763 |
} |
| 764 |
UPDATE { |
| 765 |
set NEW($1) $OLD($1) |
| 766 |
incr NEW($1) |
| 767 |
} |
| 768 |
default { |
| 769 |
return OK |
| 770 |
} |
| 771 |
} |
| 772 |
return [array get NEW] |
| 773 |
$$ LANGUAGE pltcl; |
| 774 |
|
| 775 |
CREATE TABLE mytab (num integer, description text, modcnt integer); |
| 776 |
|
| 777 |
CREATE TRIGGER trig_mytab_modcount BEFORE INSERT OR UPDATE ON mytab |
| 778 |
FOR EACH ROW EXECUTE FUNCTION trigfunc_modcount('modcnt'); |
| 779 |
</programlisting> |
| 780 |
|
| 781 |
Notice that the trigger function itself does not know the column |
| 782 |
name; that's supplied from the trigger arguments. This lets the |
| 783 |
trigger function be reused with different tables. |
| 784 |
</para> |
| 785 |
</sect1> |
| 786 |
|
| 787 |
<sect1 id="pltcl-event-trigger"> |
| 788 |
<title>Event Trigger Functions in PL/Tcl</title> |
| 789 |
|
| 790 |
<indexterm> |
| 791 |
<primary>event trigger</primary> |
| 792 |
<secondary>in PL/Tcl</secondary> |
| 793 |
</indexterm> |
| 794 |
|
| 795 |
<para> |
| 796 |
Event trigger functions can be written in PL/Tcl. |
| 797 |
<productname>PostgreSQL</productname> requires that a function that is |
| 798 |
to be called as an event trigger must be declared as a function with no |
| 799 |
arguments and a return type of <literal>event_trigger</literal>. |
| 800 |
</para> |
| 801 |
<para> |
| 802 |
The information from the trigger manager is passed to the function body |
| 803 |
in the following variables: |
| 804 |
|
| 805 |
<variablelist> |
| 806 |
|
| 807 |
<varlistentry> |
| 808 |
<term><varname>$TG_event</varname></term> |
| 809 |
<listitem> |
| 810 |
<para> |
| 811 |
The name of the event the trigger is fired for. |
| 812 |
</para> |
| 813 |
</listitem> |
| 814 |
</varlistentry> |
| 815 |
|
| 816 |
<varlistentry> |
| 817 |
<term><varname>$TG_tag</varname></term> |
| 818 |
<listitem> |
| 819 |
<para> |
| 820 |
The command tag for which the trigger is fired. |
| 821 |
</para> |
| 822 |
</listitem> |
| 823 |
</varlistentry> |
| 824 |
</variablelist> |
| 825 |
</para> |
| 826 |
|
| 827 |
<para> |
| 828 |
The return value of the trigger function is ignored. |
| 829 |
</para> |
| 830 |
|
| 831 |
<para> |
| 832 |
Here's a little example event trigger function that simply raises |
| 833 |
a <literal>NOTICE</literal> message each time a supported command is |
| 834 |
executed: |
| 835 |
|
| 836 |
<programlisting> |
| 837 |
CREATE OR REPLACE FUNCTION tclsnitch() RETURNS event_trigger AS $$ |
| 838 |
elog NOTICE "tclsnitch: $TG_event $TG_tag" |
| 839 |
$$ LANGUAGE pltcl; |
| 840 |
|
| 841 |
CREATE EVENT TRIGGER tcl_a_snitch ON ddl_command_start EXECUTE FUNCTION tclsnitch(); |
| 842 |
</programlisting> |
| 843 |
</para> |
| 844 |
</sect1> |
| 845 |
|
| 846 |
<sect1 id="pltcl-error-handling"> |
| 847 |
<title>Error Handling in PL/Tcl</title> |
| 848 |
|
| 849 |
<indexterm> |
| 850 |
<primary>exceptions</primary> |
| 851 |
<secondary>in PL/Tcl</secondary> |
| 852 |
</indexterm> |
| 853 |
|
| 854 |
<para> |
| 855 |
Tcl code within or called from a PL/Tcl function can raise an error, |
| 856 |
either by executing some invalid operation or by generating an error |
| 857 |
using the Tcl <function>error</function> command or |
| 858 |
PL/Tcl's <function>elog</function> command. Such errors can be caught |
| 859 |
within Tcl using the Tcl <function>catch</function> command. If an |
| 860 |
error is not caught but is allowed to propagate out to the top level of |
| 861 |
execution of the PL/Tcl function, it is reported as an SQL error in the |
| 862 |
function's calling query. |
| 863 |
</para> |
| 864 |
|
| 865 |
<para> |
| 866 |
Conversely, SQL errors that occur within PL/Tcl's |
| 867 |
<function>spi_exec</function>, <function>spi_prepare</function>, |
| 868 |
and <function>spi_execp</function> commands are reported as Tcl errors, |
| 869 |
so they are catchable by Tcl's <function>catch</function> command. |
| 870 |
(Each of these PL/Tcl commands runs its SQL operation in a |
| 871 |
subtransaction, which is rolled back on error, so that any |
| 872 |
partially-completed operation is automatically cleaned up.) |
| 873 |
Again, if an error propagates out to the top level without being caught, |
| 874 |
it turns back into an SQL error. |
| 875 |
</para> |
| 876 |
|
| 877 |
<para> |
| 878 |
Tcl provides an <varname>errorCode</varname> variable that can represent |
| 879 |
additional information about an error in a form that is easy for Tcl |
| 880 |
programs to interpret. The contents are in Tcl list format, and the |
| 881 |
first word identifies the subsystem or library reporting the error; |
| 882 |
beyond that the contents are left to the individual subsystem or |
| 883 |
library. For database errors reported by PL/Tcl commands, the first |
| 884 |
word is <literal>POSTGRES</literal>, the second word is the PostgreSQL |
| 885 |
version number, and additional words are field name/value pairs |
| 886 |
providing detailed information about the error. |
| 887 |
Fields <varname>SQLSTATE</varname>, <varname>condition</varname>, |
| 888 |
and <varname>message</varname> are always supplied |
| 889 |
(the first two represent the error code and condition name as shown |
| 890 |
in <xref linkend="errcodes-appendix"/>). |
| 891 |
Fields that may be present include |
| 892 |
<varname>detail</varname>, <varname>hint</varname>, <varname>context</varname>, |
| 893 |
<varname>schema</varname>, <varname>table</varname>, <varname>column</varname>, |
| 894 |
<varname>datatype</varname>, <varname>constraint</varname>, |
| 895 |
<varname>statement</varname>, <varname>cursor_position</varname>, |
| 896 |
<varname>filename</varname>, <varname>lineno</varname>, and |
| 897 |
<varname>funcname</varname>. |
| 898 |
</para> |
| 899 |
|
| 900 |
<para> |
| 901 |
A convenient way to work with PL/Tcl's <varname>errorCode</varname> |
| 902 |
information is to load it into an array, so that the field names become |
| 903 |
array subscripts. Code for doing that might look like |
| 904 |
<programlisting> |
| 905 |
if {[catch { spi_exec $sql_command }]} { |
| 906 |
if {[lindex $::errorCode 0] == "POSTGRES"} { |
| 907 |
array set errorArray $::errorCode |
| 908 |
if {$errorArray(condition) == "undefined_table"} { |
| 909 |
# deal with missing table |
| 910 |
} else { |
| 911 |
# deal with some other type of SQL error |
| 912 |
} |
| 913 |
} |
| 914 |
} |
| 915 |
</programlisting> |
| 916 |
(The double colons explicitly specify that <varname>errorCode</varname> |
| 917 |
is a global variable.) |
| 918 |
</para> |
| 919 |
</sect1> |
| 920 |
|
| 921 |
<sect1 id="pltcl-subtransactions"> |
| 922 |
<title>Explicit Subtransactions in PL/Tcl</title> |
| 923 |
|
| 924 |
<indexterm> |
| 925 |
<primary>subtransactions</primary> |
| 926 |
<secondary>in PL/Tcl</secondary> |
| 927 |
</indexterm> |
| 928 |
|
| 929 |
<para> |
| 930 |
Recovering from errors caused by database access as described in |
| 931 |
<xref linkend="pltcl-error-handling"/> can lead to an undesirable |
| 932 |
situation where some operations succeed before one of them fails, |
| 933 |
and after recovering from that error the data is left in an |
| 934 |
inconsistent state. PL/Tcl offers a solution to this problem in |
| 935 |
the form of explicit subtransactions. |
| 936 |
</para> |
| 937 |
|
| 938 |
<para> |
| 939 |
Consider a function that implements a transfer between two accounts: |
| 940 |
<programlisting> |
| 941 |
CREATE FUNCTION transfer_funds() RETURNS void AS $$ |
| 942 |
if [catch { |
| 943 |
spi_exec "UPDATE accounts SET balance = balance - 100 WHERE account_name = 'joe'" |
| 944 |
spi_exec "UPDATE accounts SET balance = balance + 100 WHERE account_name = 'mary'" |
| 945 |
} errormsg] { |
| 946 |
set result [format "error transferring funds: %s" $errormsg] |
| 947 |
} else { |
| 948 |
set result "funds transferred successfully" |
| 949 |
} |
| 950 |
spi_exec "INSERT INTO operations (result) VALUES ('[quote $result]')" |
| 951 |
$$ LANGUAGE pltcl; |
| 952 |
</programlisting> |
| 953 |
If the second <command>UPDATE</command> statement results in an |
| 954 |
exception being raised, this function will log the failure, but |
| 955 |
the result of the first <command>UPDATE</command> will |
| 956 |
nevertheless be committed. In other words, the funds will be |
| 957 |
withdrawn from Joe's account, but will not be transferred to |
| 958 |
Mary's account. This happens because each <function>spi_exec</function> |
| 959 |
is a separate subtransaction, and only one of those subtransactions |
| 960 |
got rolled back. |
| 961 |
</para> |
| 962 |
|
| 963 |
<para> |
| 964 |
To handle such cases, you can wrap multiple database operations in an |
| 965 |
explicit subtransaction, which will succeed or roll back as a whole. |
| 966 |
PL/Tcl provides a <function>subtransaction</function> command to manage |
| 967 |
this. We can rewrite our function as: |
| 968 |
<programlisting> |
| 969 |
CREATE FUNCTION transfer_funds2() RETURNS void AS $$ |
| 970 |
if [catch { |
| 971 |
subtransaction { |
| 972 |
spi_exec "UPDATE accounts SET balance = balance - 100 WHERE account_name = 'joe'" |
| 973 |
spi_exec "UPDATE accounts SET balance = balance + 100 WHERE account_name = 'mary'" |
| 974 |
} |
| 975 |
} errormsg] { |
| 976 |
set result [format "error transferring funds: %s" $errormsg] |
| 977 |
} else { |
| 978 |
set result "funds transferred successfully" |
| 979 |
} |
| 980 |
spi_exec "INSERT INTO operations (result) VALUES ('[quote $result]')" |
| 981 |
$$ LANGUAGE pltcl; |
| 982 |
</programlisting> |
| 983 |
Note that use of <function>catch</function> is still required for this |
| 984 |
purpose. Otherwise the error would propagate to the top level of the |
| 985 |
function, preventing the desired insertion into |
| 986 |
the <structname>operations</structname> table. |
| 987 |
The <function>subtransaction</function> command does not trap errors, it |
| 988 |
only assures that all database operations executed inside its scope will |
| 989 |
be rolled back together when an error is reported. |
| 990 |
</para> |
| 991 |
|
| 992 |
<para> |
| 993 |
A rollback of an explicit subtransaction occurs on any error reported |
| 994 |
by the contained Tcl code, not only errors originating from database |
| 995 |
access. Thus a regular Tcl exception raised inside |
| 996 |
a <function>subtransaction</function> command will also cause the |
| 997 |
subtransaction to be rolled back. However, non-error exits out of the |
| 998 |
contained Tcl code (for instance, due to <function>return</function>) do |
| 999 |
not cause a rollback. |
| 1000 |
</para> |
| 1001 |
</sect1> |
| 1002 |
|
| 1003 |
<sect1 id="pltcl-transactions"> |
| 1004 |
<title>Transaction Management</title> |
| 1005 |
|
| 1006 |
<para> |
| 1007 |
In a procedure called from the top level or an anonymous code block |
| 1008 |
(<command>DO</command> command) called from the top level it is possible |
| 1009 |
to control transactions. To commit the current transaction, call the |
| 1010 |
<literal>commit</literal> command. To roll back the current transaction, |
| 1011 |
call the <literal>rollback</literal> command. (Note that it is not |
| 1012 |
possible to run the SQL commands <command>COMMIT</command> or |
| 1013 |
<command>ROLLBACK</command> via <function>spi_exec</function> or similar. |
| 1014 |
It has to be done using these functions.) After a transaction is ended, |
| 1015 |
a new transaction is automatically started, so there is no separate |
| 1016 |
command for that. |
| 1017 |
</para> |
| 1018 |
|
| 1019 |
<para> |
| 1020 |
Here is an example: |
| 1021 |
<programlisting> |
| 1022 |
CREATE PROCEDURE transaction_test1() |
| 1023 |
LANGUAGE pltcl |
| 1024 |
AS $$ |
| 1025 |
for {set i 0} {$i < 10} {incr i} { |
| 1026 |
spi_exec "INSERT INTO test1 (a) VALUES ($i)" |
| 1027 |
if {$i % 2 == 0} { |
| 1028 |
commit |
| 1029 |
} else { |
| 1030 |
rollback |
| 1031 |
} |
| 1032 |
} |
| 1033 |
$$; |
| 1034 |
|
| 1035 |
CALL transaction_test1(); |
| 1036 |
</programlisting> |
| 1037 |
</para> |
| 1038 |
|
| 1039 |
<para> |
| 1040 |
Transactions cannot be ended when an explicit subtransaction is active. |
| 1041 |
</para> |
| 1042 |
</sect1> |
| 1043 |
|
| 1044 |
<sect1 id="pltcl-config"> |
| 1045 |
<title>PL/Tcl Configuration</title> |
| 1046 |
|
| 1047 |
<para> |
| 1048 |
This section lists configuration parameters that |
| 1049 |
affect <application>PL/Tcl</application>. |
| 1050 |
</para> |
| 1051 |
|
| 1052 |
<variablelist> |
| 1053 |
|
| 1054 |
<varlistentry id="guc-pltcl-start-proc" xreflabel="pltcl.start_proc"> |
| 1055 |
<term> |
| 1056 |
<varname>pltcl.start_proc</varname> (<type>string</type>) |
| 1057 |
<indexterm> |
| 1058 |
<primary><varname>pltcl.start_proc</varname> configuration parameter</primary> |
| 1059 |
</indexterm> |
| 1060 |
</term> |
| 1061 |
<listitem> |
| 1062 |
<para> |
| 1063 |
This parameter, if set to a nonempty string, specifies the name |
| 1064 |
(possibly schema-qualified) of a parameterless PL/Tcl function that |
| 1065 |
is to be executed whenever a new Tcl interpreter is created for |
| 1066 |
PL/Tcl. Such a function can perform per-session initialization, such |
| 1067 |
as loading additional Tcl code. A new Tcl interpreter is created |
| 1068 |
when a PL/Tcl function is first executed in a database session, or |
| 1069 |
when an additional interpreter has to be created because a PL/Tcl |
| 1070 |
function is called by a new SQL role. |
| 1071 |
</para> |
| 1072 |
|
| 1073 |
<para> |
| 1074 |
The referenced function must be written in the <literal>pltcl</literal> |
| 1075 |
language, and must not be marked <literal>SECURITY DEFINER</literal>. |
| 1076 |
(These restrictions ensure that it runs in the interpreter it's |
| 1077 |
supposed to initialize.) The current user must have permission to |
| 1078 |
call it, too. |
| 1079 |
</para> |
| 1080 |
|
| 1081 |
<para> |
| 1082 |
If the function fails with an error it will abort the function call |
| 1083 |
that caused the new interpreter to be created and propagate out to |
| 1084 |
the calling query, causing the current transaction or subtransaction |
| 1085 |
to be aborted. Any actions already done within Tcl won't be undone; |
| 1086 |
however, that interpreter won't be used again. If the language is |
| 1087 |
used again the initialization will be attempted again within a fresh |
| 1088 |
Tcl interpreter. |
| 1089 |
</para> |
| 1090 |
|
| 1091 |
<para> |
| 1092 |
Only superusers can change this setting. Although this setting |
| 1093 |
can be changed within a session, such changes will not affect Tcl |
| 1094 |
interpreters that have already been created. |
| 1095 |
</para> |
| 1096 |
</listitem> |
| 1097 |
</varlistentry> |
| 1098 |
|
| 1099 |
<varlistentry id="guc-pltclu-start-proc" xreflabel="pltclu.start_proc"> |
| 1100 |
<term> |
| 1101 |
<varname>pltclu.start_proc</varname> (<type>string</type>) |
| 1102 |
<indexterm> |
| 1103 |
<primary><varname>pltclu.start_proc</varname> configuration parameter</primary> |
| 1104 |
</indexterm> |
| 1105 |
</term> |
| 1106 |
<listitem> |
| 1107 |
<para> |
| 1108 |
This parameter is exactly like <varname>pltcl.start_proc</varname>, |
| 1109 |
except that it applies to PL/TclU. The referenced function must |
| 1110 |
be written in the <literal>pltclu</literal> language. |
| 1111 |
</para> |
| 1112 |
</listitem> |
| 1113 |
</varlistentry> |
| 1114 |
|
| 1115 |
</variablelist> |
| 1116 |
</sect1> |
| 1117 |
|
| 1118 |
<sect1 id="pltcl-procnames"> |
| 1119 |
<title>Tcl Procedure Names</title> |
| 1120 |
|
| 1121 |
<para> |
| 1122 |
In <productname>PostgreSQL</productname>, the same function name can be used for |
| 1123 |
different function definitions if the functions are placed in different |
| 1124 |
schemas, or if the number of arguments or their types |
| 1125 |
differ. Tcl, however, requires all procedure names to be distinct. |
| 1126 |
PL/Tcl deals with this by including the argument type names in the |
| 1127 |
internal Tcl procedure name, and then appending the function's object |
| 1128 |
ID (OID) to the internal Tcl procedure name if necessary to make it |
| 1129 |
different from the names of all previously-loaded functions in the |
| 1130 |
same Tcl interpreter. Thus, |
| 1131 |
<productname>PostgreSQL</productname> functions with the same name |
| 1132 |
and different argument types will be different Tcl procedures, too. This |
| 1133 |
is not normally a concern for a PL/Tcl programmer, but it might be visible |
| 1134 |
when debugging. |
| 1135 |
</para> |
| 1136 |
|
| 1137 |
<para> |
| 1138 |
For this reason among others, a PL/Tcl function cannot call another one |
| 1139 |
directly (that is, within Tcl). If you need to do that, you must go |
| 1140 |
through SQL, using <function>spi_exec</function> or a related command. |
| 1141 |
</para> |
| 1142 |
|
| 1143 |
</sect1> |
| 1144 |
</chapter> |