| PostgreSQL 8.1.23 Documentation | ||||
|---|---|---|---|---|
| Prev | Fast Backward | Chapter 38. PL/Perl - Perl Procedural Language | Fast Forward | 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. 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
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();