5 PL/Tcl is a loadable procedural language for the
6 <productname>Postgres</productname> database system
7 that enables the Tcl language to be used to create functions and
12 This package was originally written by Jan Wieck.
15 <!-- **** PL/Tcl overview **** -->
18 <title>Overview</title>
21 PL/Tcl offers most of the capabilities a function
22 writer has in the C language, except for some restrictions.
25 The good restriction is, that everything is executed in a safe
26 Tcl-interpreter. In addition to the limited command set of safe Tcl, only
27 a few commands are available to access the database over SPI and to raise
28 messages via elog(). There is no way to access internals of the
29 database backend or gaining OS-level access under the permissions of the
30 <productname>Postgres</productname> user ID like in C.
31 Thus, any unprivileged database user may be
32 permitted to use this language.
35 The other, internal given, restriction is, that Tcl procedures cannot
36 be used to create input-/output-functions for new data types.
39 The shared object for the PL/Tcl call handler is automatically built
40 and installed in the <productname>Postgres</productname>
41 library directory if the Tcl/Tk support is specified
42 in the configuration step of the installation procedure.
46 <!-- **** PL/Tcl description **** -->
49 <title>Description</title>
52 <title><productname>Postgres</productname> Functions and Tcl Procedure Names</title>
55 In <productname>Postgres</productname>, one and the
56 same function name can be used for
57 different functions as long as the number of arguments or their types
58 differ. This would collide with Tcl procedure names. To offer the same
59 flexibility in PL/Tcl, the internal Tcl procedure names contain the object
60 ID of the procedures pg_proc row as part of their name. Thus, different
61 argtype versions of the same <productname>Postgres</productname>
62 function are different for Tcl too.
68 <title>Defining Functions in PL/Tcl</title>
71 To create a function in the PL/Tcl language, use the known syntax
74 CREATE FUNCTION <replaceable>funcname</replaceable> <replaceable>argument-types</replaceable>) RETURNS <replaceable>return-type</replaceable> AS '
75 # PL/Tcl function body
79 When calling this function in a query, the arguments are given as
80 variables $1 ... $n to the Tcl procedure body. So a little max function
81 returning the higher of two int4 values would be created as:
84 CREATE FUNCTION tcl_max (int4, int4) RETURNS int4 AS '
85 if {$1 > $2} {return $1}
90 Composite type arguments are given to the procedure as Tcl arrays.
92 in the array are the attribute names of the composite
93 type. If an attribute in the actual row
94 has the NULL value, it will not appear in the array! Here is
95 an example that defines the overpaid_2 function (as found in the
96 older <productname>Postgres</productname> documentation) in PL/Tcl
99 CREATE FUNCTION overpaid_2 (EMP) RETURNS bool AS '
100 if {200000.0 < $1(salary)} {
103 if {$1(age) < 30 && 100000.0 < $1(salary)} {
114 <title>Global Data in PL/Tcl</title>
117 Sometimes (especially when using the SPI functions described later) it
118 is useful to have some global status data that is held between two
119 calls to a procedure.
120 All PL/Tcl procedures executed in one backend share the same
121 safe Tcl interpreter.
122 To help protecting PL/Tcl procedures from side effects,
123 an array is made available to each procedure via the upvar
124 command. The global name of this variable is the procedures internal
125 name and the local name is GD.
130 <title>Trigger Procedures in PL/Tcl</title>
133 Trigger procedures are defined in <productname>Postgres</productname>
135 arguments and a return type of opaque. And so are they in the PL/Tcl
139 The informations from the trigger manager are given to the procedure body
140 in the following variables:
145 <term><replaceable class="Parameter">$TG_name</replaceable></term>
148 The name of the trigger from the CREATE TRIGGER statement.
154 <term><replaceable class="Parameter">$TG_relid</replaceable></term>
157 The object ID of the table that caused the trigger procedure
164 <term><replaceable class="Parameter">$TG_relatts</replaceable></term>
167 A Tcl list of the tables field names prefixed with an empty list element.
168 So looking up an element name in the list with the lsearch Tcl command
169 returns the same positive number starting from 1 as the fields are numbered
170 in the pg_attribute system catalog.
176 <term><replaceable class="Parameter">$TG_when</replaceable></term>
179 The string BEFORE or AFTER depending on the event of the trigger call.
185 <term><replaceable class="Parameter">$TG_level</replaceable></term>
188 The string ROW or STATEMENT depending on the event of the trigger call.
194 <term><replaceable class="Parameter">$TG_op</replaceable></term>
197 The string INSERT, UPDATE or DELETE depending on the event of the
204 <term><replaceable class="Parameter">$NEW</replaceable></term>
207 An array containing the values of the new table row on INSERT/UPDATE
208 actions, or empty on DELETE.
214 <term><replaceable class="Parameter">$OLD</replaceable></term>
217 An array containing the values of the old table row on UPDATE/DELETE
218 actions, or empty on INSERT.
224 <term><replaceable class="Parameter">$GD</replaceable></term>
227 The global status data array as described above.
233 <term><replaceable class="Parameter">$args</replaceable></term>
236 A Tcl list of the arguments to the procedure as given in the
237 CREATE TRIGGER statement. The arguments are also accessible as $1 ... $n
238 in the procedure body.
247 The return value from a trigger procedure is one of the strings OK or SKIP,
248 or a list as returned by the 'array get' Tcl command. If the return value
249 is OK, the normal operation (INSERT/UPDATE/DELETE) that fired this trigger
250 will take place. Obviously, SKIP tells the trigger manager to silently
251 suppress the operation. The list from 'array get' tells PL/Tcl
252 to return a modified row to the trigger manager that will be inserted instead
253 of the one given in $NEW (INSERT/UPDATE only). Needless to say that all
254 this is only meaningful when the trigger is BEFORE and FOR EACH ROW.
257 Here's a little example trigger procedure that forces an integer value
258 in a table to keep track of the # of updates that are performed on the
259 row. For new row's inserted, the value is initialized to 0 and then
260 incremented on every update operation:
263 CREATE FUNCTION trigfunc_modcount() RETURNS OPAQUE AS '
276 return [array get NEW]
279 CREATE TABLE mytab (num int4, modcnt int4, desc text);
281 CREATE TRIGGER trig_mytab_modcount BEFORE INSERT OR UPDATE ON mytab
282 FOR EACH ROW EXECUTE PROCEDURE trigfunc_modcount('modcnt');
289 <title>Database Access from PL/Tcl</title>
292 The following commands are available to access the database from
293 the body of a PL/Tcl procedure:
299 <term>elog <replaceable>level</replaceable> <replaceable>msg</replaceable></term>
302 Fire a log message. Possible levels are NOTICE, WARN, ERROR,
303 FATAL, DEBUG and NOIND
304 like for the <function>elog</function> C function.
310 <term>quote <replaceable>string</replaceable></term>
313 Duplicates all occurences of single quote and backslash characters.
314 It should be used when variables are used in the query string given
315 to <function>spi_exec</function> or
316 <function>spi_prepare</function> (not for the value list on
317 <function>spi_execp</function>).
318 Think about a query string like
321 "SELECT '$val' AS ret"
324 where the Tcl variable val actually contains "doesn't". This would result
325 in the final query string
328 "SELECT 'doesn't' AS ret"
331 what would cause a parse error during
332 <function>spi_exec</function> or
333 <function>spi_prepare</function>.
337 "SELECT 'doesn''t' AS ret"
340 and has to be written as
343 "SELECT '[ quote $val ]' AS ret"
350 <term>spi_exec ?-count <replaceable>n</replaceable>? ?-array <replaceable>name</replaceable>? <replaceable>query</replaceable> ?<replaceable>loop-body</replaceable>?</term>
353 Call parser/planner/optimizer/executor for query.
354 The optional -count value tells <function>spi_exec</function>
355 the maximum number of rows
356 to be processed by the query.
360 a SELECT statement and the optional loop-body (a body of Tcl commands
361 like in a foreach statement) is given, it is evaluated for each
362 row selected and behaves like expected on continue/break. The values
363 of selected fields are put into variables named as the column names. So a
366 spi_exec "SELECT count(*) AS cnt FROM pg_proc"
369 will set the variable $cnt to the number of rows in the pg_proc system
370 catalog. If the option -array is given, the column values are stored
371 in the associative array named 'name' indexed by the column name
372 instead of individual variables.
375 spi_exec -array C "SELECT * FROM pg_class" {
376 elog DEBUG "have table $C(relname)"
380 will print a DEBUG log message for every row of pg_class. The return value
381 of <function>spi_exec</function> is the number of rows
382 affected by query as found in
383 the global variable SPI_processed.
389 <term>spi_prepare <replaceable>query</replaceable> <replaceable>typelist</replaceable></term>
392 Prepares AND SAVES a query plan for later execution. It is a bit different
393 from the C level SPI_prepare in that the plan is automatically copied to the
394 toplevel memory context. Thus, there is currently no way of preparing a
395 plan without saving it.
398 If the query references arguments, the type names must be given as a Tcl
399 list. The return value from spi_prepare is a query ID to be used in
400 subsequent calls to spi_execp. See spi_execp for a sample.
406 <term>spi_exec ?-count <replaceable>n</replaceable>? ?-array<replaceable>name</replaceable>? ?-nulls<replaceable>string</replaceable>? <replaceable>query</replaceable> ?<replaceable>value-list</replaceable>? ?<replaceable>loop-body</replaceable>?</term>
409 Execute a prepared plan from spi_prepare with variable substitution.
410 The optional -count value tells spi_execp the maximum number of rows
411 to be processed by the query.
414 The optional value for -nulls is a string of spaces and 'n' characters
415 telling spi_execp which of the values are NULL's. If given, it must
416 have exactly the length of the number of values.
419 The queryid is the ID returned by the spi_prepare call.
422 If there was a typelist given to spi_prepare, a Tcl list of values of
423 exactly the same length must be given to spi_execp after the query. If
424 the type list on spi_prepare was empty, this argument must be omitted.
427 If the query is a SELECT statement, the same as described for spi_exec
428 happens for the loop-body and the variables for the fields selected.
431 Here's an example for a PL/Tcl function using a prepared plan:
434 CREATE FUNCTION t1_count(int4, int4) RETURNS int4 AS '
435 if {![ info exists GD(plan) ]} {
436 # prepare the saved plan on the first call
437 set GD(plan) [ spi_prepare \\
438 "SELECT count(*) AS cnt FROM t1 WHERE num >= \\$1 AND num <= \\$2" \\
441 spi_execp -count 1 $GD(plan) [ list $1 $2 ]
446 Note that each backslash that Tcl should see must be doubled in
447 the query creating the function, since the main parser processes
448 backslashes too on CREATE FUNCTION.
449 Inside the query string given to spi_prepare should
450 really be dollar signs to mark the parameter positions and to not let
451 $1 be substituted by the value given in the first function call.
458 Modules and the unknown command
462 PL/Tcl has a special support for things often used. It recognizes two
463 magic tables, pltcl_modules and pltcl_modfuncs.
464 If these exist, the module 'unknown' is loaded into the interpreter
465 right after creation. Whenever an unknown Tcl procedure is called,
466 the unknown proc is asked to check if the procedure is defined in one
467 of the modules. If this is true, the module is loaded on demand.
468 To enable this behavior, the PL/Tcl call handler must be compiled
469 with -DPLTCL_UNKNOWN_SUPPORT set.
472 There are support scripts to maintain these tables in the modules
473 subdirectory of the PL/Tcl source including the source for the
474 unknown module that must get installed initially.
486 <!-- Keep this comment at the end of the file
491 sgml-minimize-attributes:nil
492 sgml-always-quote-attributes:t
495 sgml-parent-document:nil
496 sgml-default-dtd-file:"./reference.ced"
497 sgml-exposed-tags:nil
498 sgml-local-catalogs:("/usr/lib/sgml/CATALOG")
499 sgml-local-ecat-files:nil