From cf77a2a034d68b05c3f6c12d4bc5dc6e51da1d73 Mon Sep 17 00:00:00 2001 From: Tom Lane Date: Wed, 23 Jan 2002 21:08:17 +0000 Subject: [PATCH] Overhaul PL/Tcl documentation. --- doc/src/sgml/pltcl.sgml | 681 ++++++++++++++++++++++++---------------- 1 file changed, 412 insertions(+), 269 deletions(-) diff --git a/doc/src/sgml/pltcl.sgml b/doc/src/sgml/pltcl.sgml index 8234f1d68b..395861a81f 100644 --- a/doc/src/sgml/pltcl.sgml +++ b/doc/src/sgml/pltcl.sgml @@ -1,5 +1,5 @@ @@ -16,7 +16,7 @@ $Header: /cvsroot/pgsql/doc/src/sgml/pltcl.sgml,v 2.17 2001/11/21 05:53:41 thoma PL/Tcl is a loadable procedural language for the PostgreSQL database system - that enables the Tcl language to be used to create functions and + that enables the Tcl language to be used to write functions and trigger procedures. @@ -66,7 +66,9 @@ $Header: /cvsroot/pgsql/doc/src/sgml/pltcl.sgml,v 2.17 2001/11/21 05:53:41 thoma library directory if Tcl/Tk support is specified in the configuration step of the installation procedure. To install PL/Tcl and/or PL/TclU in a particular database, use the - createlang script. + createlang script, for example + createlang pltcl dbname or + createlang pltclu dbname. @@ -76,23 +78,7 @@ $Header: /cvsroot/pgsql/doc/src/sgml/pltcl.sgml,v 2.17 2001/11/21 05:53:41 thoma Description - <productname>PostgreSQL</productname> Functions and Tcl Procedure Names - - - In PostgreSQL, one and the - same function name can be used for - different functions as long as the number of arguments or their types - differ. This would collide with Tcl procedure names. To offer the same - flexibility in PL/Tcl, the internal Tcl procedure names contain the object - ID of the procedure's pg_proc row as part of their name. Thus, different - argtype versions of the same PostgreSQL - function are different for Tcl too. - - - - - - Defining Functions in PL/Tcl + PL/Tcl Functions and Arguments To create a function in the PL/Tcl language, use the standard syntax @@ -103,32 +89,68 @@ CREATE FUNCTION funcname (argument-types ' LANGUAGE 'pltcl'; - When the function is called, the arguments are given as + PL/TclU is the same, except that the language should be specified as + 'pltclu'. + + + + The body of the function is simply a piece of Tcl script. + When the function is called, the argument values are passed as variables $1 ... $n to the - Tcl procedure body. The result is returned + Tcl script. The result is returned from the Tcl code in the usual way, with a return statement. For example, a function - returning the higher of two int4 values could be defined as: + returning the greater of two integer values could be defined as: -CREATE FUNCTION tcl_max (int4, int4) RETURNS int4 AS ' +CREATE FUNCTION tcl_max (integer, integer) RETURNS integer AS ' + if {$1 > $2} {return $1} + return $2 +' LANGUAGE 'pltcl' WITH (isStrict); + + + Note the clause WITH (isStrict), which saves us from + having to think about NULL input values: if a NULL is passed, the + function will not be called at all, but will just return a NULL + result automatically. + + + + In a non-strict function, + if the actual value of an argument is NULL, the corresponding + $n variable will be set to an empty string. + To detect whether a particular argument is NULL, use the function + argisnull. For example, suppose that we wanted tcl_max + with one null and one non-null argument to return the non-null + argument, rather than NULL: + + +CREATE FUNCTION tcl_max (integer, integer) RETURNS integer AS ' + if {[argisnull 1]} { + if {[argisnull 2]} { return_null } + return $2 + } + if {[argisnull 2]} { return $1 } if {$1 > $2} {return $1} return $2 ' LANGUAGE 'pltcl'; + - To return a NULL value from a PL/Tcl function, execute - return_null. + + As shown above, + to return a NULL value from a PL/Tcl function, execute + return_null. This can be done whether the + function is strict or not. - Composite type arguments are given to the procedure as Tcl arrays. - The element names - in the array are the attribute names of the composite - type. If an attribute in the actual row + Composite-type arguments are passed to the procedure as Tcl arrays. + The element names of the array are the attribute names of the composite + type. If an attribute in the passed row has the NULL value, it will not appear in the array! Here is an example that defines the overpaid_2 function (as found in the - older PostgreSQL documentation) in PL/Tcl + older PostgreSQL documentation) in PL/Tcl: CREATE FUNCTION overpaid_2 (EMP) RETURNS bool AS ' @@ -143,27 +165,298 @@ CREATE FUNCTION overpaid_2 (EMP) RETURNS bool AS ' + + There is not currently any support for returning a composite-type + result value. + + + + + + Data Values in PL/Tcl + + + The argument values supplied to a PL/Tcl function's script are simply + the input arguments converted to text form (just as if they had been + displayed by a SELECT statement). Conversely, the return + command will accept any string that is acceptable input format for + the function's declared return type. So, the PL/Tcl programmer can + manipulate data values as if they were just text. + + Global Data in PL/Tcl - Sometimes (especially when using the SPI functions described later) it + Sometimes it is useful to have some global status data that is held between two - calls to a procedure. This is easily done since + calls to a procedure or is shared between different procedures. + This is easily done since all PL/Tcl procedures executed in one backend share the same - safe Tcl interpreter. + safe Tcl interpreter. So, any global Tcl variable is accessible to + all PL/Tcl procedure calls, and will persist for the duration of the + SQL client connection. (Note that PL/TclU functions likewise share + global data, but they are in a different Tcl interpreter and cannot + communicate with PL/Tcl functions.) - To help protect PL/Tcl procedures from unwanted side effects, - an array is made available to each procedure via the upvar + To help protect PL/Tcl procedures from unintentionally interfering + with each other, a global + array is made available to each procedure via the upvar command. The global name of this variable is the procedure's internal - name and the local name is GD. It is recommended that GD be used + name and the local name is GD. It is recommended that + GD be used for private status data of a procedure. Use regular Tcl global variables only for values that you specifically intend to be shared among multiple procedures. + + + An example of using GD appears in the + spi_execp example below. + + + + + Database Access from PL/Tcl + + + The following commands are available to access the database from + the body of a PL/Tcl procedure: + + + + + + spi_exec ?-count n? ?-array name? query ?loop-body? + + + Execute an SQL query given as a string. An error in the query + causes an error to be raised. Otherwise, the command's return value + is the number of rows processed (selected, inserted, updated, or + deleted) by the query, or zero if the query is a utility + statement. In addition, if the query is a SELECT statement, the + values of the selected columns are placed in Tcl variables as + described below. + + + The optional -count value tells + spi_exec the maximum number of rows + to process in the query. The effect of this is comparable to + setting up the query as a cursor and then saying FETCH n. + + + If the query is a SELECT statement, the values of the SELECT's + result columns are placed into Tcl variables named after the columns. + If the -array option is given, the column values are + instead stored into the named associative array, with the SELECT + column names used as array indexes. + + + If the query is a SELECT statement and no loop-body + script is given, then only the first row of results are stored into + Tcl variables; remaining rows, if any, are ignored. No store occurs + if the + SELECT returns no rows (this case can be detected by checking the + result of spi_exec). For example, + + +spi_exec "SELECT count(*) AS cnt FROM pg_proc" + + + will set the Tcl variable $cnt to the number of rows in + the pg_proc system catalog. + + + If the optional loop-body argument is given, it is + a piece of Tcl script that is executed once for each row in the + SELECT result (note: loop-body is ignored if the given + query is not a SELECT). The values of the current row's fields + are stored into Tcl variables before each iteration. For example, + + +spi_exec -array C "SELECT * FROM pg_class" { + elog DEBUG "have table $C(relname)" +} + + + will print a DEBUG log message for every row of pg_class. This + feature works similarly to other Tcl looping constructs; in + particular continue and break work in the + usual way inside the loop body. + + + If a field of a SELECT result is NULL, the target + variable for it is unset rather than being set. + + + + + + spi_prepare query typelist + + + Prepares and saves a query plan for later execution. The saved plan + will be retained for the life of the current backend. + + + The query may use arguments, which are placeholders for + values to be supplied whenever the plan is actually executed. + In the query string, refer to arguments + by the symbols $1 ... $n. + If the query uses arguments, the names of the argument types + must be given as a Tcl list. (Write an empty list for + typelist if no arguments are used.) + Presently, the argument types must be identified by the internal + type names shown in pg_type; for example int4 not + integer. + + + The return value from spi_prepare is a query ID + to be used in subsequent calls to spi_execp. See + spi_execp for an example. + + + + + + spi_execp ?-count n? ?-array name? ?-nulls string? queryid ?value-list? ?loop-body? + + + Execute a query previously prepared with spi_prepare. + queryid is the ID returned by + spi_prepare. If the query references arguments, + a value-list must be supplied: this + is a Tcl list of actual values for the arguments. This must be + the same length as the argument type list previously given to + spi_prepare. Omit value-list + if the query has no arguments. + + + The optional value for -nulls is a string of spaces and + 'n' characters telling spi_execp + which of the arguments are NULLs. If given, it must have exactly the + same length as the value-list. If it + is not given, all the argument values are non-NULL. + + + Except for the way in which the query and its arguments are specified, + spi_execp works just like spi_exec. + The -count, -array, and + loop-body options are the same, + and so is the result value. + + + Here's an example of a PL/Tcl function using a prepared plan: + + +CREATE FUNCTION t1_count(integer, integer) RETURNS integer AS ' + if {![ info exists GD(plan) ]} { + # prepare the saved plan on the first call + set GD(plan) [ spi_prepare \\ + "SELECT count(*) AS cnt FROM t1 WHERE num >= \\$1 AND num <= \\$2" \\ + [ list int4 int4 ] ] + } + spi_execp -count 1 $GD(plan) [ list $1 $2 ] + return $cnt +' LANGUAGE 'pltcl'; + + + Note that each backslash that Tcl should see must be doubled when + we type in the function, since the main parser processes + backslashes too in CREATE FUNCTION. We need backslashes inside + the query string given to spi_prepare to ensure that + the $n markers will be passed through to + spi_prepare as-is, and not + replaced by Tcl variable substitution. + + + + + + + spi_lastoid + + spi_lastoid + + + Returns the OID of the row inserted by the last + spi_exec'd or spi_execp'd query, + if that query was a single-row INSERT. (If not, you get zero.) + + + + + + quote string + + + Duplicates all occurrences of single quote and backslash characters + in the given string. This may be used to safely quote strings + that are to be inserted into SQL queries given + to spi_exec or + spi_prepare. + For example, think about a query string like + + +"SELECT '$val' AS ret" + + + where the Tcl variable val actually contains + doesn't. This would result + in the final query string + + +SELECT 'doesn't' AS ret + + + which would cause a parse error during + spi_exec or + spi_prepare. + The submitted query should contain + + +SELECT 'doesn''t' AS ret + + + which can be formed in PL/Tcl as + + +"SELECT '[ quote $val ]' AS ret" + + + One advantage of spi_execp is that you don't + have to quote argument values like this, since the arguments are never + parsed as part of an SQL query string. + + + + + + + elog + + elog level msg + + + Emit a log or error message. Possible levels are DEBUG, + NOTICE, ERROR, and FATAL. + DEBUG and NOTICE simply emit the given message + into the postmaster log (and send it to the client too, in the case of + NOTICE). ERROR raises an error condition: + further execution of the function is abandoned, and the current + transaction is aborted. FATAL aborts the transaction and + causes the current backend to shut down (there is probably no good + reason to use this error level in PL/Tcl functions, but it's provided + for completeness). + + + + + + @@ -175,13 +468,13 @@ CREATE FUNCTION overpaid_2 (EMP) RETURNS bool AS ' - Trigger procedures are defined in PostgreSQL - as functions without - arguments and a return type of opaque. And so are they in the PL/Tcl - language. + Trigger procedures can be written in PL/Tcl. As is customary in + PostgreSQL, a procedure that's to be called + as a trigger must be declared as a function with no arguments + and a return type of opaque. - The information from the trigger manager is given to the procedure body + The information from the trigger manager is passed to the procedure body in the following variables: @@ -209,10 +502,11 @@ CREATE FUNCTION overpaid_2 (EMP) RETURNS bool AS ' $TG_relatts - A Tcl list of the tables field names prefixed with an empty list element. - So looking up an element name in the list with the lsearch Tcl command - returns the same positive number starting from 1 as the fields are numbered - in the pg_attribute system catalog. + A Tcl list of the table field names, prefixed with an empty list + element. So looking up an element name in the list with Tcl's + lsearch command returns the element's number starting + with 1 for the first column, the same way the fields are customarily + numbered in PostgreSQL. @@ -221,7 +515,8 @@ CREATE FUNCTION overpaid_2 (EMP) RETURNS bool AS ' $TG_when - The string BEFORE or AFTER depending on the event of the trigger call. + The string BEFORE or AFTER depending on the + type of trigger call. @@ -230,7 +525,8 @@ CREATE FUNCTION overpaid_2 (EMP) RETURNS bool AS ' $TG_level - The string ROW or STATEMENT depending on the event of the trigger call. + The string ROW or STATEMENT depending on the + type of trigger call. @@ -239,8 +535,8 @@ CREATE FUNCTION overpaid_2 (EMP) RETURNS bool AS ' $TG_op - The string INSERT, UPDATE or DELETE depending on the event of the - trigger call. + The string INSERT, UPDATE or + DELETE depending on the type of trigger call. @@ -249,8 +545,9 @@ CREATE FUNCTION overpaid_2 (EMP) RETURNS bool AS ' $NEW - An array containing the values of the new table row on INSERT/UPDATE - actions, or empty on DELETE. + An associative array containing the values of the new table row for + INSERT/UPDATE actions, or empty for DELETE. The array is indexed + by field name. Fields that are NULL will not appear in the array! @@ -259,17 +556,9 @@ CREATE FUNCTION overpaid_2 (EMP) RETURNS bool AS ' $OLD - An array containing the values of the old table row on UPDATE/DELETE - actions, or empty on INSERT. - - - - - - $GD - - - The global status data array as described above. + An associative array containing the values of the old table row for + UPDATE/DELETE actions, or empty for INSERT. The array is indexed + by field name. Fields that are NULL will not appear in the array! @@ -279,8 +568,8 @@ CREATE FUNCTION overpaid_2 (EMP) RETURNS bool AS ' A Tcl list of the arguments to the procedure as given in the - CREATE TRIGGER statement. The arguments are also accessible as $1 ... $n - in the procedure body. + CREATE TRIGGER statement. These arguments are also accessible as + $1 ... $n in the procedure body. @@ -289,14 +578,16 @@ CREATE FUNCTION overpaid_2 (EMP) RETURNS bool AS ' - The return value from a trigger procedure is one of the strings OK or SKIP, - or a list as returned by the 'array get' Tcl command. If the return value - is OK, the normal operation (INSERT/UPDATE/DELETE) that fired this trigger - will take place. Obviously, SKIP tells the trigger manager to silently - suppress the operation. The list from 'array get' tells PL/Tcl - to return a modified row to the trigger manager that will be inserted instead - of the one given in $NEW (INSERT/UPDATE only). Needless to say that all - this is only meaningful when the trigger is BEFORE and FOR EACH ROW. + The return value from a trigger procedure can be one of the strings + OK or SKIP, or a list as returned by the + array get Tcl command. If the return value is OK, + the operation (INSERT/UPDATE/DELETE) that fired the trigger will proceed + normally. SKIP tells the trigger manager to silently suppress + the operation for this row. If a list is returned, it tells PL/Tcl to + return a modified row to the trigger manager that will be inserted + instead of the one given in $NEW (this works for INSERT/UPDATE + only). Needless to say that all this is only meaningful when the trigger + is BEFORE and FOR EACH ROW; otherwise the return value is ignored. Here's a little example trigger procedure that forces an integer value @@ -321,222 +612,74 @@ CREATE FUNCTION trigfunc_modcount() RETURNS OPAQUE AS ' return [array get NEW] ' LANGUAGE 'pltcl'; -CREATE TABLE mytab (num int4, modcnt int4, description text); +CREATE TABLE mytab (num integer, description text, modcnt integer); CREATE TRIGGER trig_mytab_modcount BEFORE INSERT OR UPDATE ON mytab FOR EACH ROW EXECUTE PROCEDURE trigfunc_modcount('modcnt'); + Notice that the trigger procedure itself does not know the column + name; that's supplied from the trigger arguments. This lets the + trigger procedure be re-used with different tables. - Database Access from PL/Tcl - - - The following commands are available to access the database from - the body of a PL/Tcl procedure: - - - - - - - elog - - elog level msg - - - Fire a log message. Possible levels are NOTICE, ERROR, - FATAL, and DEBUG - as for the elog C function. - - - - - - quote string - - - Duplicates all occurrences of single quote and backslash characters. - It should be used when variables are used in the query string given - to spi_exec or - spi_prepare (not for the value list on - spi_execp). - Think about a query string like - - -"SELECT '$val' AS ret" - - - where the Tcl variable val actually contains doesn't. This would result - in the final query string - - -SELECT 'doesn't' AS ret - - - which would cause a parse error during - spi_exec or - spi_prepare. - It should contain - - -SELECT 'doesn''t' AS ret - - - and has to be written as - - -SELECT '[ quote $val ]' AS ret - - - - - - - - spi_lastoid - - spi_lastoid - - - Returns the OID of the last query if it was an INSERT. - - - - - - spi_exec ?-count n? ?-array name? query ?loop-body? - - - Call parser/planner/optimizer/executor for query. - The optional -count value tells spi_exec - the maximum number of rows - to be processed by the query. - - - If the query is - a SELECT statement and the optional loop-body (a body of Tcl commands - like in a foreach statement) is given, it is evaluated for each - row selected and behaves like expected on continue/break. The values - of selected fields are put into variables named as the column names. So a - - -spi_exec "SELECT count(*) AS cnt FROM pg_proc" - - - will set the variable $cnt to the number of rows in the pg_proc system - catalog. If the option -array is given, the column values are stored - in the associative array named 'name' indexed by the column name - instead of individual variables. - - -spi_exec -array C "SELECT * FROM pg_class" { - elog DEBUG "have table $C(relname)" -} - - - will print a DEBUG log message for every row of pg_class. The return value - of spi_exec is the number of rows - affected by the query as found in - the global variable SPI_processed. - - - - - - spi_prepare query typelist - - - Prepares AND SAVES a query plan for later execution. It is a bit different - from the C level SPI_prepare in that the plan is automatically copied to the - top-level memory context. Thus, there is currently no way of preparing a - plan without saving it. - - - If the query references arguments, the type names must be given as a Tcl - list. The return value from spi_prepare is a query ID to be used in - subsequent calls to spi_execp. See spi_execp for a sample. - - - - - - spi_execp ?-count n? ?-arrayname? ?-nullsstring? queryid ?value-list? ?loop-body? - - - Execute a prepared plan from spi_prepare with variable substitution. - The optional -count value tells spi_execp the maximum number of rows - to be processed by the query. - + Modules and the <function>unknown</> command - The optional value for -nulls is a string of spaces and 'n' characters - telling spi_execp which of the values are NULL's. If given, it must - have exactly the length of the number of values. + PL/Tcl has support for auto-loading Tcl code when used. + It recognizes a special table, pltcl_modules, which + is presumed to contain modules of Tcl code. If this table + exists, the module unknown is fetched from the table + and loaded into the Tcl interpreter immediately after creating + the interpreter. - The queryid is the ID returned by the spi_prepare call. + While the unknown module could actually contain any + initialization script you need, it normally defines a Tcl + unknown procedure that is invoked whenever Tcl does + not recognize an invoked procedure name. PL/Tcl's standard version + of this procedure tries to find a module in pltcl_modules + that will define the required procedure. If one is found, it is + loaded into the interpreter, and then execution is allowed to + proceed with the originally attempted procedure call. A + secondary table pltcl_modfuncs provides an index of + which functions are defined by which modules, so that the lookup + is reasonably quick. - If there was a typelist given to spi_prepare, a Tcl list of values of - exactly the same length must be given to spi_execp after the query. If - the type list on spi_prepare was empty, this argument must be omitted. + The PostgreSQL distribution includes + support scripts to maintain these tables: + pltcl_loadmod, pltcl_listmod, + pltcl_delmod, as well as source for the standard + unknown module share/unknown.pltcl. This module + must be loaded + into each database initially to support the autoloading mechanism. - If the query is a SELECT statement, the same as described for spi_exec - happens for the loop-body and the variables for the fields selected. + The tables pltcl_modules and pltcl_modfuncs + must be readable by all, but it is wise to make them owned and + writable only by the database administrator. - - Here's an example for a PL/Tcl function using a prepared plan: - - -CREATE FUNCTION t1_count(int4, int4) RETURNS int4 AS ' - if {![ info exists GD(plan) ]} { - # prepare the saved plan on the first call - set GD(plan) [ spi_prepare \\ - "SELECT count(*) AS cnt FROM t1 WHERE num >= \\$1 AND num <= \\$2" \\ - int4 ] - } - spi_execp -count 1 $GD(plan) [ list $1 $2 ] - return $cnt -' LANGUAGE 'pltcl'; - - - Note that each backslash that Tcl should see must be doubled in - the query creating the function, since the main parser processes - backslashes too on CREATE FUNCTION. - Inside the query string given to spi_prepare should - really be dollar signs to mark the parameter positions and to not let - $1 be substituted by the value given in the first function call. - - - - - - - - Modules and the <function>unknown</> command - - PL/Tcl has a special support for things often used. It - recognizes two magic tables, pltcl_modules and - pltcl_modfuncs. If these exist, the module - 'unknown' is loaded into the interpreter right after - creation. Whenever an unknown Tcl procedure is called, the - unknown proc is asked to check if the procedure is defined in - one of the modules. If this is true, the module is loaded on - demand. - - - There are support scripts to maintain these tables: - pltcl_loadmod,pltcl_listmod, - pltcl_delmod and source for the - unknown module share/unknown.pltcl that must be loaded - into database initially for getting unknown support. - + Tcl Procedure Names + + + In PostgreSQL, one and the + same function name can be used for + different functions as long as the number of arguments or their types + differ. Tcl, however, requires all procedure names to be distinct. + PL/Tcl deals with this by making the internal Tcl procedure names contain + the object + ID of the procedure's pg_proc row as part of their name. Thus, + PostgreSQL functions with the same name + and different argument types will be different Tcl procedures too. This + is not normally a concern for a PL/Tcl programmer, but it might be visible + when debugging. + + -- 2.40.0