If you want to write trigger functions using Perl, then PL/Perl allows you to do all the good stuff that you have learned so far using PL/PgSQL. Let's rewrite an example we demonstrated in Chapter 5, PL/pgSQL Trigger Functions, in PL/Perl. Recall the simple, "Hey, I am called" trigger. The PL/Perl version of the example looks as shown in the following code. We probably don't need to provide a more complex example, as this simple example demonstrates the PL/Perl syntax in a sufficient way:
CREATE OR REPLACE FUNCTION notify_trigger_plperl() RETURNS TRIGGER AS $$ $result = sprintf('Hi, I got %s invoked FOR %s %s %s on %s', $_TD->{name}, $_TD->{level}, $_TD->{when}, $_TD->{event}, $_TD->{table_name} ); if(($_TD->{event} cmp 'UPDATE') == 0){ $result .= sprintf(' OLD = %s AND NEW=%s', $_TD->{old}{i}, $_TD->{new}{i}); $_TD->{new}{i} = $_TD->{old}{i} + $_TD->{new}{i}; elog(NOTICE, $result); return "MODIFY"; } elsif(($_TD->{event} cmp 'DELETE') == 0){ elog(NOTICE, "Skipping Delete"); return "SKIP"; } elog(NOTICE, $result); $$ LANGUAGE plperl; CREATE TABLE notify_test_plperl(i int); CREATE TRIGGER notify_insert_plperl_trigger BEFORE INSERT OR UPDATE OR DELETE ON notify_test_plperl FOR EACH ROW EXECUTE PROCEDURE notify_trigger_plperl();
Let's try to run the INSERT
, UPDATE
, and DELETE
commands:
testdb=# INSERT INTO notify_test_plperl VALUES(1); NOTICE: Hi, I got notify_insert_plperl_trigger invoked FOR ROW BEFORE INSERT on notify_test_plperl CONTEXT: PL/Perl function "notify_trigger_plperl" INSERT 0 1 testdb=# UPDATE notify_test_plperl SET i = 10; NOTICE: Hi, I got notify_insert_plperl_trigger invoked FOR ROW BEFORE UPDATE on notify_test_plperl OLD = 1 AND NEW=10 CONTEXT: PL/Perl function "notify_trigger_plperl" UPDATE 1 testdb=# select * from notify_test_plperl; i ---- 11 (1 row) postgres=# DELETE FROM notify_test_plperl; NOTICE: Skipping Delete CONTEXT: PL/Perl function "notify_trigger_plperl" DELETE 0
Let's review what we have done so far. We have created a trigger function, a test table, and an actual trigger that runs before an INSERT
, UPDATE
, or DELETE
for each row.
The trigger function uses a couple of things we have not discussed so far. In a PL/Perl trigger function, the hash reference $_TD
contains information about the current trigger event. You can see the full list of keys in $_TD
at http://www.postgresql.org/docs/current/static/plperl-triggers.html.
The triggers we have used in our example are explained in the following table:
Trigger name |
Trigger description |
---|---|
|
This denotes the name of the trigger. In our example, this will contain |
|
This is a |
|
The |
|
The |
|
This denotes the name of the table. In our example, this will contain |
|
This will contain the |
|
This will contain the |
We have also used a utility function called elog()
in the trigger function. This function emits log messages. The level values that are possible are DEBUG
, LOG
, INFO
, NOTICE
, WARNING
, and ERROR
. The ERROR
value propagates an error to the calling query and is similar to a Perl die
command.
You can view all the available built-ins and utility functions available in PL/Perl at http://www.postgresql.org/docs/current/static/plperl-builtins.html.
Our trigger function returns the special values "MODIFY"
and "SKIP"
in the case of UPDATE
and DELETE
, respectively. If a PL/Perl trigger function returns "MODIFY"
, it means that the NEW
value has been modified by the trigger function. If a trigger function modifies a NEW
value but does not return "MODIFY"
, then the change done by the function will be discarded. The "SKIP"
implies that the operation should not be executed.
3.15.17.74