Writing PL/Perl triggers

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

$_TD->{name}

This denotes the name of the trigger. In our example, this will contain notify_trigger_plperl.

$_TD->{level}

This is a ROW or STATEMENT trigger. In our example, this will contain ROW.

$_TD->{when}

The BEFORE, AFTER, or INSTEAD OF trigger. In our example, this will contain BEFORE.

$_TD->{event}

The INSERT, UPDATE, DELETE, or TRUNCATE command. In our example, this will contain INSERT, UPDATE, or DELETE.

$_TD->{table_name}

This denotes the name of the table. In our example, this will contain notify_test_plperl.

$_TD->{old}{i}

This will contain the OLD value of the column i. In our example, this will contain 1.

TD->{new}{i}

This will contain the NEW value of the column i. In our example, this will contain 10.

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.

Note

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.

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
3.15.17.74