39.6. PL/Perl Triggers

PL/Perl can be used to write trigger functions. In a trigger function, the hash reference $_TD contains information about the current trigger event. $_TD is a global variable, which gets a separate local value for each invocation of the trigger. The fields of the $_TD hash reference are:


NEW value of column foo


OLD value of column foo


Name of the trigger being called


Trigger event: INSERT , UPDATE , DELETE , or UNKNOWN


When the trigger was called: BEFORE , AFTER , or UNKNOWN


The trigger level: ROW , STATEMENT , or UNKNOWN


OID of the table on which the trigger fired


Name of the table on which the trigger fired


Name of the table on which the trigger fired. This has been deprecated, and could be removed in a future release. Please use $_TD->{table_name} instead.


Name of the schema in which the table on which the trigger fired, is


Number of arguments of the trigger function


Arguments of the trigger function. Does not exist if $_TD->{argc} is 0.

Triggers can return one of the following:


Execute the statement


Don't execute the statement


Indicates that the NEW row was modified by the trigger function

Here is an example of a trigger function, illustrating some of the above:

CREATE TABLE test ( i int, v varchar ); CREATE OR REPLACE FUNCTION valid_id() RETURNS trigger AS $$ if (($_TD->{new}{i} >= 100) || ($_TD->{new}{i} <= 0)) { return "SKIP"; # skip INSERT/UPDATE command } elsif ($_TD->{new}{v} ne "immortal") { $_TD->{new}{v} .= "(modified by trigger)"; return "MODIFY"; # modify row and execute INSERT/UPDATE command } else { return; # execute INSERT/UPDATE command } $$ LANGUAGE plperl; CREATE TRIGGER test_valid_id_trig BEFORE INSERT OR UPDATE ON test FOR EACH ROW EXECUTE PROCEDURE valid_id();