2 <Title>Extending <Acronym>SQL</Acronym>: Procedural languages</Title>
5 * General information about procedural language support
10 Beginning with the release of version 6.3,
11 <ProductName>Postgres</ProductName> supports
12 the definition of procedural languages.
13 In the case of a function or trigger
14 procedure defined in a procedural language, the database has
15 no builtin knowlege how to interpret the functions source
16 text. Instead, the calls are passed into
17 a handler that knows the details of the language. The
18 handler itself is a special programming language function
19 compiled into a shared object
24 * Installation of procedural languages
29 <Title>Installing Procedural Languages</Title>
34 Procedural Language Installation
38 A procedural language is installed in the database in three steps.
40 <Step Performance="Required">
42 The shared object for the language handler
43 must be compiled and installed. By default the
44 handler for PL/pgSQL is built and installed into the
45 database library directory. If Tcl/Tk support is
46 configured in, the handler for PL/Tcl is also built
47 and installed in the same location.
50 Writing a handler for a new procedural language (PL)
51 is outside the scope of this manual.
54 <Step Performance="Required">
56 The handler must be declared with the command
58 CREATE FUNCTION <Replaceable>handler_function_name</Replaceable> () RETURNS OPAQUE AS
59 '<Filename>path-to-shared-object</Filename>' LANGUAGE 'C';
61 The special return type of <Acronym>OPAQUE</Acronym> tells
62 the database, that this function does not return one of
63 the defined base- or composite types and is not directly usable
64 in <Acronym>SQL</Acronym> statements.
67 <Step Performance="Required">
69 The PL must be declared with the command
71 CREATE [ TRUSTED ] PROCEDURAL LANGUAGE '<Replaceable>language-name</Replaceable>'
72 HANDLER <Replaceable>handler_function_name</Replaceable>
73 LANCOMPILER '<Replaceable>description</Replaceable>';
75 The optional keyword <Acronym>TRUSTED</Acronym> tells
76 if ordinary database users that have no superuser
77 privileges can use this language to create functions
78 and trigger procedures. Since PL functions are
79 executed inside the database backend it should only be used for
80 languages that don't gain access to database backends
81 internals or the filesystem. The languages PL/pgSQL and
82 PL/Tcl are known to be trusted.
88 <Title>Example</Title>
89 <Step Performance="Required">
91 The following command tells the database where to find the
92 shared object for the PL/pgSQL languages call handler function.
95 CREATE FUNCTION plpgsql_call_handler () RETURNS OPAQUE AS
96 '/usr/local/pgsql/lib/plpgsql.so' LANGUAGE 'C';
100 <Step Performance="Required">
105 CREATE TRUSTED PROCEDURAL LANGUAGE 'plpgsql'
106 HANDLER plpgsql_call_handler
107 LANCOMPILER 'PL/pgSQL';
110 then defines that the previously declared call handler
111 function should be invoked for functions and trigger procedures
112 where the language attribute is 'plpgsql'.
115 PL handler functions have a special call interface that is
116 different from regular C language functions. One of the arguments
117 given to the handler is the object ID in the <FileName>pg_proc</FileName>
118 tables entry for the function that should be executed.
119 The handler examines various system catalogs to analyze the
120 functions call arguments and it's return data type. The source
121 text of the functions body is found in the prosrc attribute of
122 <FileName>pg_proc</FileName>.
123 Due to this, in contrast to C language functions, PL functions
124 can be overloaded like SQL language functions. There can be
125 multiple different PL functions having the same function name,
126 as long as the call arguments differ.
129 Procedural languages defined in the <FileName>template1</FileName>
130 database are automatically defined in all subsequently created
131 databases. So the database administrator can decide which
132 languages are available by default.
136 </Sect1> <!-- **** End of PL installation **** -->
139 * The procedural language PL/pgSQL
144 <Title>PL/pgSQL</Title>
147 PL/pgSQL is a loadable procedural language for the
148 <ProductName>Postgres</ProductName> database system.
152 This package was originally written by Jan Wieck.
155 <!-- **** PL/pgSQL overview **** -->
158 <Title>Overview</Title>
161 The design goals of PL/pgSQL were to create a loadable procedural
166 can be used to create functions and trigger procedures,
171 adds control structures to the <Acronym>SQL</Acronym> language,
176 can perform complex computations,
181 inherits all user defined types, functions and operators,
186 can be defined to be trusted by the server,
197 The PL/pgSQL call handler parses the functions source text and
198 produces an internal binary instruction tree on the first time, the
199 function is called by a backend. The produced bytecode is identified
200 in the call handler by the object ID of the function. This ensures,
201 that changing a function by a DROP/CREATE sequence will take effect
202 without establishing a new database connection.
205 For all expressions and <Acronym>SQL</Acronym> statements used in
206 the function, the PL/pgSQL bytecode interpreter creates a
207 prepared execution plan using the SPI managers SPI_prepare() and
208 SPI_saveplan() functions. This is done the first time, the individual
209 statement is processed in the PL/pgSQL function. Thus, a function with
210 conditional code that contains many statements for which execution
211 plans would be required, will only prepare and save those plans
212 that are really used during the entire lifetime of the database
216 Except for input-/output-conversion and calculation functions
217 for user defined types, anything that can be defined in C language
218 functions can also be done with PL/pgSQL. It is possible to
219 create complex conditional computation functions and later use
220 them to define operators or use them in functional indices.
224 <!-- **** PL/pgSQL Description **** -->
227 <Title>Description</Title>
229 <!-- **** PL/pgSQL structure **** -->
232 <Title>Structure of PL/pgSQL</Title>
235 The PL/pgSQL language is case insensitive. All keywords and
236 identifiers can be used in mixed upper- and lowercase.
239 PL/pgSQL is a block oriented language. A block is defined as
242 [<<label>>]
244 <replaceable>declarations</replaceable>]
246 <replaceable>statements</replaceable>
250 There can be any number of subblocks in the statement section
251 of a block. Subblocks can be used to hide variables from outside a
252 block of statements. The variables
253 declared in the declarations section preceding a block are
254 initialized to their default values every time the block is entered,
255 not only once per function call.
259 It is important not to misunderstand the meaning of BEGIN/END for
260 grouping statements in PL/pgSQL and the database commands for
261 transaction control. Functions and trigger procedures cannot
262 start or commit transactions and <ProductName>Postgres</ProductName>
263 does not have nested transactions.
267 <!-- **** PL/pgSQL comments **** -->
270 <Title>Comments</Title>
273 There are two types of comments in PL/pgSQL. A double dash '--'
274 starts a comment that extends to the end of the line. A '/*'
275 starts a block comment that extends to the next occurence of '*/'.
276 Block comments cannot be nested, but double dash comments can be
277 enclosed into a block comment and a double dash can hide
278 the block comment delimiters '/*' and '*/'.
282 <!-- **** PL/pgSQL declarations **** -->
285 <Title>Declarations</Title>
288 All variables, rows and records used in a block or it's
289 subblocks must be declared in the declarations section of a block
290 except for the loop variable of a FOR loop iterating over a range
291 of integer values. Parameters given to a PL/pgSQL function are
292 automatically declared with the usual identifiers $n.
293 The declarations have the following syntax:
299 <Replaceable>name</Replaceable> [ CONSTANT ] <Replaceable>type</Replaceable> [ NOT NULL ] [ DEFAULT | := <Replaceable>value</Replaceable> ];
303 Declares a variable of the specified base type. If the variable
304 is declared as CONSTANT, the value cannot be changed. If NOT NULL
305 is specified, an assignment of a NULL value results in a runtime
306 error. Since the default value of all variables is the
307 <Acronym>SQL</Acronym> NULL value, all variables declared as NOT NULL
308 must also have a default value specified.
311 The default value is evaluated ever time the function is called. So
312 assigning '<Replaceable>now</Replaceable>' to a variable of type
313 <Replaceable>datetime</Replaceable> causes the variable to have the
314 time of the actual function call, not when the function was
315 precompiled into it's bytecode.
322 <Replaceable>name</Replaceable> <Replaceable>class</Replaceable>%ROWTYPE;
326 Declares a row with the structure of the given class. Class must be
327 an existing table- or viewname of the database. The fields of the row
328 are accessed in the dot notation. Parameters to a function can
329 be composite types (complete table rows). In that case, the
330 corresponding identifier $n will be a rowtype, but it
331 must be aliased using the ALIAS command described below. Only the user
332 attributes of a table row are accessible in the row, no Oid or other
333 system attributes (hence the row could be from a view and view rows
334 don't have useful system attributes).
337 The fields of the rowtype inherit the tables fieldsizes
338 or precision for char() etc. data types.
345 <Replaceable>name</Replaceable> RECORD;
349 Records are similar to rowtypes, but they have no predefined structure.
350 They are used in selections and FOR loops to hold one actual
351 database row from a SELECT operation. One and the same record can be
352 used in different selections. Accessing a record or an attempt to assign
353 a value to a record field when there is no actual row in it results
357 The NEW and OLD rows in a trigger are given to the procedure as
358 records. This is necessary because in <ProductName>Postgres</ProductName>
359 one and the same trigger procedure can handle trigger events for
367 <Replaceable>name</Replaceable> ALIAS FOR $n;
372 For better readability of the code it is possible to define an alias
373 for a positional parameter to a function.
376 This aliasing is required for composite types given as arguments to
377 a function. The dot notation $1.salary as in SQL functions is not
385 RENAME <Replaceable>oldname</Replaceable> TO <Replaceable>newname</Replaceable>;
390 Change the name of a variable, record or row. This is useful
391 if NEW or OLD should be referenced by another name inside a
400 <!-- **** PL/pgSQL data types **** -->
403 <Title>Data Types</Title>
406 The type of a varible can be any of the existing basetypes of
407 the database. <Replaceable>type</Replaceable> in the declarations
408 section above is defined as:
414 <ProductName>Postgres</ProductName>-basetype
419 <Replaceable>variable</Replaceable>%TYPE
424 <Replaceable>class.field</Replaceable>%TYPE
430 <Replaceable>variable</Replaceable> is the name of a variable,
431 previously declared in the
432 same function, that is visible at this point.
435 <Replaceable>class</Replaceable> is the name of an existing table
436 or view where <Replaceable>field</Replaceable> is the name of
440 Using the <Replaceable>class.field</Replaceable>%TYPE
441 causes PL/pgSQL to lookup the attributes definitions at the
442 first call to the funciton during the lifetime of a backend.
443 Have a table with a char(20) attribute and some PL/pgSQL functions
444 that deal with it's content in local variables. Now someone
445 decides that char(20) isn't enough, dumps the table, drops it,
446 recreates it now with the attribute in question defined as
447 char(40) and restores the data. Ha - he forgot about the
448 funcitons. The computations inside them will truncate the values
449 to 20 characters. But if they are defined using the
450 <Replaceable>class.field</Replaceable>%TYPE
451 declarations, they will automagically handle the size change or
452 if the new table schema defines the attribute as text type.
456 <!-- **** PL/pgSQL expressions **** -->
459 <Title>Expressions</Title>
462 All expressions used in PL/pgSQL statements are processed using
463 the backends executor. Expressions which appear to contain
464 constants may in fact require run-time evaluation (e.g. 'now' for the
466 it is impossible for the PL/pgSQL parser
467 to identify real constant values other than the NULL keyword. All
468 expressions are evaluated internally by executing a query
470 SELECT <Replaceable>expression</Replaceable>
472 using the SPI manager. In the expression, occurences of variable
473 identifiers are substituted by parameters and the actual values from
474 the variables are passed to the executor in the parameter array. All
475 expressions used in a PL/pgSQL function are only prepared and
479 The type checking done by the <productname>Postgres</productname>
480 main parser has some side
481 effects to the interpretation of constant values. In detail there
482 is a difference between what the two functions
485 CREATE FUNCTION logfunc1 (text) RETURNS datetime AS '
489 INSERT INTO logtable VALUES (logtxt, ''now'');
492 ' LANGUAGE 'plpgsql';
498 CREATE FUNCTION logfunc2 (text) RETURNS datetime AS '
504 INSERT INTO logtable VALUES (logtxt, curtime);
507 ' LANGUAGE 'plpgsql';
510 do. In the case of logfunc1(), the <ProductName>Postgres</ProductName>
512 knows when preparing the plan for the INSERT, that the string 'now'
513 should be interpreted as datetime because the target field of logtable
514 is of that type. Thus, it will make a constant from it at this time
515 and this constant value is then used in all invocations of logfunc1()
516 during the lifetime of the backend. Needless to say that this isn't what the
520 In the case of logfunc2(), the <ProductName>Postgres</ProductName>
521 main parser does not know
522 what type 'now' should become and therefor it returns a datatype of
523 text containing the string 'now'. During the assignment
524 to the local variable curtime, the PL/pgSQL interpreter casts this
525 string to the datetime type by calling the text_out() and datetime_in()
526 functions for the conversion.
529 This type checking done by the <ProductName>Postgres</ProductName> main
530 parser got implemented after PL/pgSQL was nearly done.
531 It is a difference between 6.3 and 6.4 and affects all functions
532 using the prepared plan feature of the SPI manager.
534 variable in the above manner is currently the only way in PL/pgSQL to get
535 those values interpreted correctly.
538 If record fields are used in expressions or statements, the data types of
539 fields should not change between calls of one and the same expression.
540 Keep this in mind when writing trigger procedures that handle events
541 for more than one table.
545 <!-- **** PL/pgSQL statements **** -->
548 <Title>Statements</Title>
551 Anything not understood by the PL/pgSQL parser as specified below
552 will be put into a query and sent down to the database engine
553 to execute. The resulting query should not return any data.
564 An assignment of a value to a variable or row/record field is
567 <Replaceable>identifier</Replaceable> := <Replaceable>expression</Replaceable>;
569 If the expressions result data type doesn't match the variables
570 data type, or the variable has a size/precision that is known
571 (as for char(20)), the result value will be implicitly casted by
572 the PL/pgSQL bytecode interpreter using the result types output- and
573 the variables type input-functions. Note that this could potentially
574 result in runtime errors generated by the types input functions.
577 An assignment of a complete selection into a record or row can
580 SELECT <Replaceable>expressions</Replaceable> INTO <Replaceable>target</Replaceable> FROM ...;
582 <Replaceable>target</Replaceable> can be a record, a row variable or a
583 comma separated list of variables and record-/row-fields.
586 if a row or a variable list is used as target, the selected values
587 must exactly match the structure of the target(s) or a runtime error
588 occurs. The FROM keyword can be followed by any valid qualification,
589 grouping, sorting etc. that can be given for a SELECT statement.
592 There is a special variable named FOUND of type bool that can be used
593 immediately after a SELECT INTO to check if an assignment had success.
596 SELECT * INTO myrec FROM EMP WHERE empname = myname;
598 RAISE EXCEPTION ''employee % not found'', myname;
602 If the selection returns multiple rows, only the first is moved
603 into the target fields. All others are silently discarded.
610 Calling another function
614 All functions defined in a <ProductName>Prostgres</ProductName>
615 database return a value. Thus, the normal way to call a function
616 is to execute a SELECT query or doing an assignment (resulting
617 in a PL/pgSQL internal SELECT). But there are cases where someone
618 isn't interested int the functions result.
620 PERFORM <Replaceable>query</Replaceable>
622 executes a 'SELECT <Replaceable>query</Replaceable>' over the
623 SPI manager and discards the result. Identifiers like local
624 variables are still substituted into parameters.
631 Returning from the function
636 RETURN <Replaceable>expression</Replaceable>
638 The function terminates and the value of <Replaceable>expression</Replaceable>
639 will be returned to the upper executor. The return value of a function
640 cannot be undefined. If control reaches the end of the toplevel block
641 of the function without hitting a RETURN statement, a runtime error
645 The expressions result will be automatically casted into the
646 functions return type as described for assignments.
653 Aborting and messages
657 As indicated in the above examples there is a RAISE statement that
658 can throw messages into the <ProductName>Postgres</ProductName>
661 RAISE <replaceable class="parameter">level</replaceable> ''<replaceable class="parameter">format</replaceable>'' [, <replaceable class="parameter">identifier</replaceable> [...]];
663 Inside the format, <quote>%</quote> is used as a placeholder for the
664 subsequent comma-separated identifiers. Possible levels are
665 DEBUG (silently suppressed in production running databases), NOTICE
666 (written into the database log and forwarded to the client application)
667 and EXCEPTION (written into the database log and aborting the transaction).
679 IF <Replaceable>expression</Replaceable> THEN
680 <replaceable>statements</replaceable>
682 <replaceable>statements</replaceable>]
685 The <Replaceable>expression</Replaceable> must return a value that
686 at least can be casted into a boolean type.
697 There are multiple types of loops.
699 [<<label>>]
701 <replaceable>statements</replaceable>
704 An unconditional loop that must be terminated explicitly
705 by an EXIT statement. The optional label can be used by
706 EXIT statements of nested loops to specify which level of
707 nesting should be terminated.
709 [<<label>>]
710 WHILE <Replaceable>expression</Replaceable> LOOP
711 <replaceable>statements</replaceable>
714 A conditional loop that is executed as long as the evaluation
715 of <Replaceable>expression</Replaceable> is true.
717 [<<label>>]
718 FOR <Replaceable>name</Replaceable> IN [ REVERSE ] <Replaceable>expression</Replaceable> .. <Replaceable>expression</Replaceable> LOOP
719 <replaceable>statements</replaceable>
722 A loop that iterates over a range of integer values. The variable
723 <Replaceable>name</Replaceable> is automatically created as type
724 integer and exists only inside the loop. The two expressions giving
725 the lower and upper bound of the range are evaluated only when entering
726 the loop. The iteration step is always 1.
728 [<<label>>]
729 FOR <Replaceable>record | row</Replaceable> IN <Replaceable>select_clause</Replaceable> LOOP
730 <replaceable>statements</replaceable>
733 The record or row is assigned all the rows resulting from the select
734 clause and the statements executed for each. If the loop is terminated
735 with an EXIT statement, the last assigned row is still accessible
738 EXIT [ <Replaceable>label</Replaceable> ] [ WHEN <Replaceable>expression</Replaceable> ];
740 If no <Replaceable>label</Replaceable> given,
741 the innermost loop is terminated and the
742 statement following END LOOP is executed next.
743 If <Replaceable>label</Replaceable> is given, it
744 must be the label of the current or an upper level of nested loop
745 blocks. Then the named loop or block is terminated and control
746 continues with the statement after the loops/blocks corresponding
756 <!-- **** PL/pgSQL trigger procedures **** -->
759 <Title>Trigger Procedures</Title>
762 PL/pgSQL can be used to define trigger procedures. They are created
763 with the usual CREATE FUNCTION command as a function with no
764 arguments and a return type of OPAQUE.
767 There are some <ProductName>Postgres</ProductName> specific details
768 in functions used as trigger procedures.
771 First they have some special variables created automatically in the
772 toplevel blocks declaration section. They are
783 Datatype RECORD; variable holding the new database row on INSERT/UPDATE
784 operations on ROW level triggers.
795 Datatype RECORD; variable holding the old database row on UPDATE/DELETE
796 operations on ROW level triggers.
807 Datatype name; variable that contains the name of the trigger actually
819 Datatype text; a string of either 'BEFORE' or 'AFTER' depending on the
831 Datatype text; a string of either 'ROW' or 'STATEMENT' depending on the
843 Datatype text; a string of 'INSERT', 'UPDATE' or 'DELETE' telling
844 for which operation the trigger is actually fired.
855 Datatype oid; the object ID of the table that caused the
867 Datatype name; the name of the table that caused the trigger
879 Datatype integer; the number of arguments given to the trigger
880 procedure in the CREATE TRIGGER statement.
891 Datatype array of text; the arguments from the CREATE TRIGGER statement.
892 The index counts from 0 and can be given as an expression. Invalid
893 indices (< 0 or >= tg_nargs) result in a NULL value.
901 Second they must return either NULL or a record/row containing
902 exactly the structure of the table the trigger was fired for.
903 Triggers fired AFTER might always return a NULL value with no
904 effect. Triggers fired BEFORE signal the trigger manager
905 to skip the operation for this actual row when returning NULL.
906 Otherwise, the returned record/row replaces the inserted/updated
907 row in the operation. It is possible to replace single values directly
908 in NEW and return that or to build a complete new record/row to
913 <!-- **** PL/pgSQL exceptions **** -->
916 <Title>Exceptions</Title>
919 <ProductName>Postgres</ProductName> does not have a very smart
920 exception handling model. Whenever the parser, planner/optimizer
921 or executor decide that a statement cannot be processed any longer,
922 the whole transaction gets aborted and the system jumps back
923 into the mainloop to get the next query from the client application.
926 It is possible to hook into the error mechanism to notice that this
927 happens. But currently it's impossible to tell what really
928 caused the abort (input/output conversion error, floating point
929 error, parse error). And it is possible that the database backend
930 is in an inconsistent state at this point so returning to the upper
931 executor or issuing more commands might corrupt the whole database.
932 And even if, at this point the information, that the transaction
933 is aborted, is already sent to the client application, so resuming
934 operation does not make any sense.
937 Thus, the only thing PL/pgSQL currently does when it encounters
938 an abort during execution of a function or trigger
939 procedure is to write some additional DEBUG level log messages
940 telling in which function and where (line number and type of
941 statement) this happened.
946 <!-- **** PL/pgSQL Examples **** -->
949 <Title>Examples</Title>
952 Here are only a few functions to demonstrate how easy PL/pgSQL
953 functions can be written. For more complex examples the programmer
954 might look at the regression test for PL/pgSQL.
958 One painful detail of writing functions in PL/pgSQL is the handling
959 of single quotes. The functions source text on CREATE FUNCTION must
960 be a literal string. Single quotes inside of literal strings must be
961 either doubled or quoted with a backslash. We are still looking for
962 an elegant alternative. In the meantime, doubling the single qoutes
963 as in the examples below should be used. Any solution for this
964 in future versions of <ProductName>Postgres</ProductName> will be
969 <Title>Some Simple PL/pgSQL Functions</Title>
972 The following two PL/pgSQL functions are identical to their
973 counterparts from the C language function discussion.
976 CREATE FUNCTION add_one (int4) RETURNS int4 AS '
980 ' LANGUAGE 'plpgsql';
984 CREATE FUNCTION concat_text (text, text) RETURNS text AS '
988 ' LANGUAGE 'plpgsql';
995 <Title>PL/pgSQL Function on Composite Type</Title>
998 Again it is the PL/pgSQL equivalent to the example from
1002 CREATE FUNCTION c_overpaid (EMP, int4) RETURNS bool AS '
1004 emprec ALIAS FOR $1;
1005 sallim ALIAS FOR $2;
1007 IF emprec.salary ISNULL THEN
1010 RETURN emprec.salary > sallim;
1012 ' LANGUAGE 'plpgsql';
1019 <Title>PL/pgSQL Trigger Procedure</Title>
1022 This trigger ensures, that any time a row is inserted or updated
1023 in the table, the current username and time are stamped into the
1024 row. And it ensures that an employees name is given and that the
1025 salary is a positive value.
1034 CREATE FUNCTION emp_stamp () RETURNS OPAQUE AS
1036 -- Check that empname and salary are given
1037 IF NEW.empname ISNULL THEN
1038 RAISE EXCEPTION ''empname cannot be NULL value'';
1040 IF NEW.salary ISNULL THEN
1041 RAISE EXCEPTION ''% cannot have NULL salary'', NEW.empname;
1044 -- Who works for us when she must pay for?
1045 IF NEW.salary < 0 THEN
1046 RAISE EXCEPTION ''% cannot have a negative salary'', NEW.empname;
1049 -- Remember who changed the payroll when
1050 NEW.last_date := ''now'';
1051 NEW.last_user := getpgusername();
1054 ' LANGUAGE 'plpgsql';
1056 CREATE TRIGGER emp_stamp BEFORE INSERT OR UPDATE ON emp
1057 FOR EACH ROW EXECUTE PROCEDURE emp_stamp();
1068 * The procedural language PL/Tcl
1073 <Title>PL/Tcl</Title>
1076 PL/Tcl is a loadable procedural language for the
1077 <ProductName>Postgres</ProductName> database system
1078 that enables the Tcl language to be used to create functions and
1083 This package was originally written by Jan Wieck.
1086 <!-- **** PL/Tcl overview **** -->
1089 <Title>Overview</Title>
1092 PL/Tcl offers most of the capabilities a function
1093 writer has in the C language, except for some restrictions.
1096 The good restriction is, that everything is executed in a safe
1097 Tcl-interpreter. In addition to the limited command set of safe Tcl, only
1098 a few commands are available to access the database over SPI and to raise
1099 messages via elog(). There is no way to access internals of the
1100 database backend or gaining OS-level access under the permissions of the
1101 <ProductName>Postgres</ProductName> user ID like in C.
1102 Thus, any unprivileged database user may be
1103 permitted to use this language.
1106 The other, internal given, restriction is, that Tcl procedures cannot
1107 be used to create input-/output-functions for new data types.
1110 The shared object for the PL/Tcl call handler is automatically built
1111 and installed in the <ProductName>Postgres</ProductName>
1112 library directory if the Tcl/Tk support is specified
1113 in the configuration step of the installation procedure.
1117 <!-- **** PL/Tcl description **** -->
1120 <Title>Description</Title>
1123 <Title><ProductName>Postgres</ProductName> Functions and Tcl Procedure Names</Title>
1126 In <ProductName>Postgres</ProductName>, one and the
1127 same function name can be used for
1128 different functions as long as the number of arguments or their types
1129 differ. This would collide with Tcl procedure names. To offer the same
1130 flexibility in PL/Tcl, the internal Tcl procedure names contain the object
1131 ID of the procedures pg_proc row as part of their name. Thus, different
1132 argtype versions of the same <ProductName>Postgres</ProductName>
1133 function are different for Tcl too.
1139 <Title>Defining Functions in PL/Tcl</Title>
1142 To create a function in the PL/Tcl language, use the known syntax
1145 CREATE FUNCTION <Replaceable>funcname</Replaceable> (<Replaceable>argument-types</Replaceable>) RETURNS <Replaceable>returntype</Replaceable> AS '
1146 # PL/Tcl function body
1150 When calling this function in a query, the arguments are given as
1151 variables $1 ... $n to the Tcl procedure body. So a little max function
1152 returning the higher of two int4 values would be created as:
1155 CREATE FUNCTION tcl_max (int4, int4) RETURNS int4 AS '
1156 if {$1 > $2} {return $1}
1161 Composite type arguments are given to the procedure as Tcl arrays.
1163 in the array are the attribute names of the composite
1164 type. If an attribute in the actual row
1165 has the NULL value, it will not appear in the array! Here is
1166 an example that defines the overpaid_2 function (as found in the
1167 older <ProductName>Postgres</ProductName> documentation) in PL/Tcl
1170 CREATE FUNCTION overpaid_2 (EMP) RETURNS bool AS '
1171 if {200000.0 < $1(salary)} {
1174 if {$1(age) < 30 && 100000.0 < $1(salary)} {
1185 <Title>Global Data in PL/Tcl</Title>
1188 Sometimes (especially when using the SPI functions described later) it
1189 is useful to have some global status data that is held between two
1190 calls to a procedure.
1191 All PL/Tcl procedures executed in one backend share the same
1192 safe Tcl interpreter.
1193 To help protecting PL/Tcl procedures from side effects,
1194 an array is made available to each procedure via the upvar
1195 command. The global name of this variable is the procedures internal
1196 name and the local name is GD.
1201 <Title>Trigger Procedures in PL/Tcl</Title>
1204 Trigger procedures are defined in <ProductName>Postgres</ProductName>
1205 as functions without
1206 arguments and a return type of opaque. And so are they in the PL/Tcl
1210 The informations from the trigger manager are given to the procedure body
1211 in the following variables:
1216 <Term><Replaceable class="Parameter">
1218 </Replaceable></Term>
1221 The name of the trigger from the CREATE TRIGGER statement.
1227 <Term><Replaceable class="Parameter">
1229 </Replaceable></Term>
1232 The object ID of the table that caused the trigger procedure
1239 <Term><Replaceable class="Parameter">
1241 </Replaceable></Term>
1244 A Tcl list of the tables field names prefixed with an empty list element.
1245 So looking up an element name in the list with the lsearch Tcl command
1246 returns the same positive number starting from 1 as the fields are numbered
1247 in the pg_attribute system catalog.
1253 <Term><Replaceable class="Parameter">
1255 </Replaceable></Term>
1258 The string BEFORE or AFTER depending on the event of the trigger call.
1264 <Term><Replaceable class="Parameter">
1266 </Replaceable></Term>
1269 The string ROW or STATEMENT depending on the event of the trigger call.
1275 <Term><Replaceable class="Parameter">
1277 </Replaceable></Term>
1280 The string INSERT, UPDATE or DELETE depending on the event of the
1287 <Term><Replaceable class="Parameter">
1289 </Replaceable></Term>
1292 An array containing the values of the new table row on INSERT/UPDATE
1293 actions, or empty on DELETE.
1299 <Term><Replaceable class="Parameter">
1301 </Replaceable></Term>
1304 An array containing the values of the old table row on UPDATE/DELETE
1305 actions, or empty on INSERT.
1311 <Term><Replaceable class="Parameter">
1313 </Replaceable></Term>
1316 The global status data array as described above.
1322 <Term><Replaceable class="Parameter">
1324 </Replaceable></Term>
1327 A Tcl list of the arguments to the procedure as given in the
1328 CREATE TRIGGER statement. The arguments are also accessible as $1 ... $n
1329 in the procedure body.
1337 The return value from a trigger procedure is one of the strings OK or SKIP,
1338 or a list as returned by the 'array get' Tcl command. If the return value
1339 is OK, the normal operation (INSERT/UPDATE/DELETE) that fired this trigger
1340 will take place. Obviously, SKIP tells the trigger manager to silently
1341 suppress the operation. The list from 'array get' tells PL/Tcl
1342 to return a modified row to the trigger manager that will be inserted instead
1343 of the one given in $NEW (INSERT/UPDATE only). Needless to say that all
1344 this is only meaningful when the trigger is BEFORE and FOR EACH ROW.
1347 Here's a little example trigger procedure that forces an integer value
1348 in a table to keep track of the # of updates that are performed on the
1349 row. For new row's inserted, the value is initialized to 0 and then
1350 incremented on every update operation:
1353 CREATE FUNCTION trigfunc_modcount() RETURNS OPAQUE AS '
1359 set NEW($1) $OLD($1)
1366 return [array get NEW]
1369 CREATE TABLE mytab (num int4, modcnt int4, desc text);
1371 CREATE TRIGGER trig_mytab_modcount BEFORE INSERT OR UPDATE ON mytab
1372 FOR EACH ROW EXECUTE PROCEDURE trigfunc_modcount('modcnt');
1379 <Title>Database Access from PL/Tcl</Title>
1382 The following commands are available to access the database from
1383 the body of a PL/Tcl procedure:
1390 elog <Replaceable>level</Replaceable> <Replaceable>msg</Replaceable>
1394 Fire a log message. Possible levels are NOTICE, WARN, ERROR,
1395 FATAL, DEBUG and NOIND
1396 like for the elog() C function.
1403 quote <Replaceable>string</Replaceable>
1407 Duplicates all occurences of single quote and backslash characters.
1408 It should be used when variables are used in the query string given
1409 to spi_exec or spi_prepare (not for the value list on spi_execp).
1410 Think about a query string like
1413 "SELECT '$val' AS ret"
1416 where the Tcl variable val actually contains "doesn't". This would result
1417 in the final query string
1420 "SELECT 'doesn't' AS ret"
1423 what would cause a parse error during spi_exec or spi_prepare.
1427 "SELECT 'doesn''t' AS ret"
1430 and has to be written as
1433 "SELECT '[ quote $val ]' AS ret"
1441 spi_exec ?-count <Replaceable>n</Replaceable>? ?-array <Replaceable>name</Replaceable>? <Replaceable>query</Replaceable> ?<Replaceable>loop-body</Replaceable>?
1445 Call parser/planner/optimizer/executor for query.
1446 The optional -count value tells spi_exec the maximum number of rows
1447 to be processed by the query.
1451 a SELECT statement and the optional loop-body (a body of Tcl commands
1452 like in a foreach statement) is given, it is evaluated for each
1453 row selected and behaves like expected on continue/break. The values
1454 of selected fields are put into variables named as the column names. So a
1457 spi_exec "SELECT count(*) AS cnt FROM pg_proc"
1460 will set the variable $cnt to the number of rows in the pg_proc system
1461 catalog. If the option -array is given, the column values are stored
1462 in the associative array named 'name' indexed by the column name
1463 instead of individual variables.
1466 spi_exec -array C "SELECT * FROM pg_class" {
1467 elog DEBUG "have table $C(relname)"
1471 will print a DEBUG log message for every row of pg_class. The return value
1472 of spi_exec is the number of rows affected by query as found in
1473 the global variable SPI_processed.
1480 spi_prepare <Replaceable>query</Replaceable> <Replaceable>typelist</Replaceable>
1484 Prepares AND SAVES a query plan for later execution. It is a bit different
1485 from the C level SPI_prepare in that the plan is automatically copied to the
1486 toplevel memory context. Thus, there is currently no way of preparing a
1487 plan without saving it.
1490 If the query references arguments, the type names must be given as a Tcl
1491 list. The return value from spi_prepare is a query ID to be used in
1492 subsequent calls to spi_execp. See spi_execp for a sample.
1499 spi_exec ?-count <Replaceable>n</Replaceable>? ?-array <Replaceable>name</Replaceable>? ?-nulls <Replaceable>str</Replaceable>? <Replaceable>query</Replaceable> ?<Replaceable>valuelist</Replaceable>? ?<Replaceable>loop-body</Replaceable>?
1503 Execute a prepared plan from spi_prepare with variable substitution.
1504 The optional -count value tells spi_execp the maximum number of rows
1505 to be processed by the query.
1508 The optional value for -nulls is a string of spaces and 'n' characters
1509 telling spi_execp which of the values are NULL's. If given, it must
1510 have exactly the length of the number of values.
1513 The queryid is the ID returned by the spi_prepare call.
1516 If there was a typelist given to spi_prepare, a Tcl list of values of
1517 exactly the same length must be given to spi_execp after the query. If
1518 the type list on spi_prepare was empty, this argument must be omitted.
1521 If the query is a SELECT statement, the same as described for spi_exec
1522 happens for the loop-body and the variables for the fields selected.
1525 Here's an example for a PL/Tcl function using a prepared plan:
1528 CREATE FUNCTION t1_count(int4, int4) RETURNS int4 AS '
1529 if {![ info exists GD(plan) ]} {
1530 # prepare the saved plan on the first call
1531 set GD(plan) [ spi_prepare \\
1532 "SELECT count(*) AS cnt FROM t1 WHERE num >= \\$1 AND num <= \\$2" \\
1535 spi_execp -count 1 $GD(plan) [ list $1 $2 ]
1540 Note that each backslash that Tcl should see must be doubled in
1541 the query creating the function, since the main parser processes
1542 backslashes too on CREATE FUNCTION.
1543 Inside the query string given to spi_prepare should
1544 really be dollar signs to mark the parameter positions and to not let
1545 $1 be substituted by the value given in the first function call.
1552 Modules and the unknown command
1556 PL/Tcl has a special support for things often used. It recognizes two
1557 magic tables, pltcl_modules and pltcl_modfuncs.
1558 If these exist, the module 'unknown' is loaded into the interpreter
1559 right after creation. Whenever an unknown Tcl procedure is called,
1560 the unknown proc is asked to check if the procedure is defined in one
1561 of the modules. If this is true, the module is loaded on demand.
1562 To enable this behavior, the PL/Tcl call handler must be compiled
1563 with -DPLTCL_UNKNOWN_SUPPORT set.
1566 There are support scripts to maintain these tables in the modules
1567 subdirectory of the PL/Tcl source including the source for the
1568 unknown module that must get installed initially.