Trigger Procedures in PL/Tcl

PostgreSQL 9.3.24 Documentation
Prev	Up	Chapter 41. PL/Tcl - Tcl Procedural Language	Next

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 , AFTER , or INSTEAD OF , depending on the type of trigger event.
$TG_level: The string ROW or STATEMENT depending on the type of trigger event.
$TG_op: The string INSERT , UPDATE , DELETE , or TRUNCATE depending on the type of trigger event.
$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. This is not set for statement-level triggers.
$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. This is not set for statement-level triggers.
$args: A Tcl list of the arguments to the procedure as given in the CREATE TRIGGER statement. These arguments are also accessible as $1 ... $ n in the procedure body.

The return value from a trigger procedure can be one of the strings OK or SKIP , or a list of column name/value pairs. 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; the contents of the modified row are specified by the column names and values in the list. Any columns not mentioned in the list are set to null. Returning a modified row is only meaningful for row-level BEFORE INSERT or UPDATE triggers, for which the modified row will be inserted instead of the one given in $NEW ; or for row-level INSTEAD OF INSERT or UPDATE triggers where the returned row is used as the source data for INSERT RETURNING or UPDATE RETURNING clauses. In row-level BEFORE DELETE or INSTEAD OF DELETE triggers, returning a modified row has the same effect as returning OK , that is the operation proceeds. The trigger return value is ignored for all other types of triggers.

Tip: The result list can be made from an array representation of the modified tuple with the array get Tcl command.

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.

Prev	Home	Next
Database Access from PL/Tcl	Up	Modules and the `unknown` Command