PostgreSQL 8.0.1 Documentation
Prev	Fast Backward	Chapter 37. PL/Perl - Perl Procedural Language	Fast Forward	Next

37.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. The fields of the $_TD hash reference are:

$_TD->{new}{foo}: NEW value of column foo
$_TD->{old}{foo}: OLD value of column foo
$_TD->{name}: Name of the trigger being called
$_TD->{event}: Trigger event: INSERT, UPDATE, DELETE, or UNKNOWN
$_TD->{when}: When the trigger was called: BEFORE, AFTER, or UNKNOWN
$_TD->{level}: The trigger level: ROW, STATEMENT, or UNKNOWN
$_TD->{relid}: OID of the table on which the trigger fired
$_TD->{relname}: Name of the table on which the trigger fired
$_TD->{argc}: Number of arguments of the trigger function
@{$_TD->{args}}: Arguments of the trigger function. Does not exist if $_TD->{argc} is 0.

Triggers can return one of the following:

return;: Execute the statement
"SKIP": Don't execute the statement
"MODIFY": 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();

Prev	Home	Next
Trusted and Untrusted PL/Perl	Up	Limitations and Missing Features