If you want to write trigger functions using Tcl, then PL/PTcl allows you to do all the good stuff that you have learned so far, using PL/pgSQL, PL/Perl, and PL/Python. Let's rewrite an example we demonstrated in Chapter 5, PL/pgSQL Trigger Functions. Recall the simple, "Hey, I am called" trigger. This is how the PL/Tcl version of the example looks:
CREATE OR REPLACE FUNCTION notify_trigger_pltcl() RETURNS TRIGGER AS $$ set result [format "Hi, I got %s invoked FOR %s %s %s on %s" $TG_name $TG_level $TG_when $TG_op $TG_table_name] if {$TG_op == "UPDATE"} { append result [format " OLD = %s AND NEW=%s" $OLD(i) $NEW(i)] set NEW(i) [expr $OLD(i) + $NEW(i)] elog NOTICE $result return [array get NEW] } elseif {$TG_op == "DELETE"} { elog NOTICE "DELETE" return SKIP } elog NOTICE $result return OK $$ LANGUAGE pltcl; CREATE TABLE notify_test_pltcl(i int); CREATE TRIGGER notify_insert_pltcl_trigger BEFORE INSERT OR UPDATE OR DELETE ON notify_test_pltcl FOR EACH ROW EXECUTE PROCEDURE notify_trigger_pltcl();
The preceding code is almost a carbon copy of the one in Chapter 11, PL/Perl – Perl Procedural Language, but in the Tcl syntax. It demonstrates how to use TG
variables, how to modify NEW
values, and how to SKIP
an operation.
It prints "Hey I got invoked" with different attributes of the trigger. In the case of an UPDATE
, it also prints the NEW
and OLD
values, as well as modifies the NEW
value and skips the DELETE
operation. A PL/Tcl trigger function can return the following values:
OK
: This is the default value and implies that the operation executed by the user will proceed normallySKIP
: This return value implies that the user-executed operation will be silently ignoredLIST
: This is returned by array get
and implies that a modified version of the NEW
array should be returnedOLD
and NEW
: These values are available as associative arrays in a PL/Tcl trigger function, and the attributes of the table are the names of the elements in the arrayLet's run INSERT
, UPDATE
, and DELETE
to see the results:
postgres=# INSERT INTO notify_test_pltcl VALUES(1); NOTICE: Hi, I got notify_insert_pltcl_trigger invoked FOR ROW BEFORE INSERT on notify_test_pltcl INSERT 0 1 postgres=# UPDATE notify_test_pltcl SET i=10; NOTICE: Hi, I got notify_insert_pltcl_trigger invoked FOR ROW BEFORE UPDATE on notify_test_pltcl OLD = 1 AND NEW=10 UPDATE 1 postgres=# DELETE FROM notify_test_pltcl; NOTICE: DELETE DELETE 0 postgres=# SELECT * FROM notify_test_pltcl; i ---- 11 (1 row)
In a PL/Tcl trigger function, the $TG
variables contain information about the current trigger event. You can see the full list of variables at http://www.postgresql.org/docs/current/interactive/pltcl-trigger.html.
The variables we have used in our example are explained in the following table:
3.142.197.212