Trigger procedures can be written in PL/Tcl.
PostgreSQL requires that a procedure that is to be called
as a trigger must be declared as a function with no arguments
and a return type of trigger
.
The information from the trigger manager is passed 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.
$TG_table_name
The name of the table that caused the trigger procedure to be invoked.
$TG_table_schema
The schema of the table that caused the trigger procedure to be invoked.
$TG_relatts
A Tcl list of the table column names, prefixed with an empty list
element. So looking up a column 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 columns are customarily
numbered in PostgreSQL. (Empty list
elements also appear in the positions of columns that have been
dropped, so that the attribute numbering is correct for columns
to their right.)
$TG_when
The string BEFORE
or AFTER
depending on the
type of trigger call.
$TG_level
The string ROW
or STATEMENT
depending on the
type of trigger call.
$TG_op
The string INSERT
, UPDATE
, or
DELETE
depending on the type of trigger call.
$NEW
An associative array containing the values of the new table
row for INSERT
or UPDATE
actions, or
empty for DELETE
. The array is indexed by column
name. Columns that are null will not appear in the array.
$OLD
An associative array containing the values of the old table
row for UPDATE
or DELETE
actions, or
empty for INSERT
. The array is indexed by column
name. Columns that are null will not appear in the array.
$args
A Tcl list of the arguments to the procedure as given in the
CREATE TRIGGER
statement. These arguments are also accessible as
$1
... $
in the procedure body.
n
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
and 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 in a table to keep track of the number of updates that are performed on the row. For new rows inserted, the value is initialized to 0 and then incremented on every update operation.
CREATE FUNCTION trigfunc_modcount() RETURNS trigger AS $$ switch $TG_op { INSERT { set NEW($1) 0 } UPDATE { set NEW($1) $OLD($1) incr NEW($1) } default { return OK } } return [array get NEW] $$ LANGUAGE pltcl; 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 reused with different tables.