| PostgreSQL 9.5.25 Documentation | |||
|---|---|---|---|
| Prev | Up | Chapter 42. PL/Perl - Perl Procedural Language | Next |
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, TRUNCATE, or UNKNOWN
When the trigger was called: BEFORE, AFTER, INSTEAD OF, 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.
Row-level triggers can return one of the following:
Execute the operation
Don't execute the operation
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();