Download PostgreSQL User`s Guide - BITS

Chapter 11. PL/Tcl - TCL Procedural Language
When calling this function in a query, the arguments are given as variables $1 ... $n to the Tcl
procedure body. So a little max function returning the higher of two int4 values would be
created as:
CREATE FUNCTION tcl_max (int4, int4) RETURNS int4 AS ’
if {$1 > $2} {return $1}
return $2
’ LANGUAGE ’pltcl’;
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 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 Postgres documentation) in PL/Tcl
CREATE FUNCTION overpaid_2 (EMP) RETURNS bool AS ’
if {200000.0 < $1(salary)} {
return "t"
}
if {$1(age) < 30 && 100000.0 < $1(salary)} {
return "t"
}
return "f"
’ LANGUAGE ’pltcl’;
Global Data in PL/Tcl
Sometimes (especially when using the SPI functions described later) it is useful to have some
global status data that is held between two calls to a procedure. All PL/Tcl procedures executed
in one backend share the same safe Tcl interpreter. To help protecting PL/Tcl procedures from
side effects, an array is made available to each procedure via the upvar command. The global
name of this variable is the procedures internal name and the local name is GD.
Trigger Procedures in PL/Tcl
Trigger procedures are defined in Postgres as functions without arguments and a return type of
opaque. And so are they in the PL/Tcl language.
The informations from the trigger manager are given to the procedure body in the following
variables:
$TG_name
The name of the trigger from the CREATE TRIGGER statement.
$TG_relid
The object ID of the table that caused the trigger procedure to be invoked.
89