This page in other versions: 8.4 / 9.0 / 9.1 / 9.2 / 9.3  |  Development versions: devel  |  Unsupported versions: 8.0 / 8.1 / 8.2 / 8.3

42.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:

$_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, TRUNCATE, or UNKNOWN

$_TD->{when}

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

$_TD->{level}

The trigger level: ROW, STATEMENT, or UNKNOWN

$_TD->{relid}

OID of the table on which the trigger fired

$_TD->{table_name}

Name of the table on which the trigger fired

$_TD->{relname}

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.

$_TD->{table_schema}

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

$_TD->{argc}

Number of arguments of the trigger function

@{$_TD->{args}}

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

Row-level triggers can return one of the following:

return;

Execute the operation

"SKIP"

Don't execute the operation

"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();

Add Comment

Please use this form to add your own comments regarding your experience with particular features of PostgreSQL, clarifications of the documentation, or hints for other users. Please note, this is not a support forum, and your IP address will be logged. If you have a question or need help, please see the faq, try a mailing list, or join us on IRC. Note that submissions containing URLs or other keywords commonly found in 'spam' comments may be silently discarded. Please contact the webmaster if you think this is happening to you in error.

Proceed to the comment form.

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group