43.6. Trigger Functions in PL/Tcl

43.6. Trigger Functions in PL/Tcl
Prev	Up	Chapter 43. PL/Tcl — Tcl Procedural Language	Home	Next

+ Trigger functions can be written in PL/Tcl. + PostgreSQL requires that a function 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 function 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 function + to be invoked. +
$TG_table_name: + The name of the table that caused the trigger function + to be invoked. +
$TG_table_schema: + The schema of the table that caused the trigger function + 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 function as given in the + CREATE TRIGGER statement. These arguments are also accessible as + $1 ... $n in the function body. +

+ The return value from a trigger function 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 function 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 FUNCTION trigfunc_modcount('modcnt');
+

+ + Notice that the trigger function itself does not know the column + name; that's supplied from the trigger arguments. This lets the + trigger function be reused with different tables. +

Prev	Up	Next
43.5. Database Access from PL/Tcl	Home	43.7. Event Trigger Functions in PL/Tcl