Writing PL/Tcl triggers

If you want to write trigger functions using Tcl, then PL/PTcl allows you to do all the good stuff that you have learned so far, using PL/pgSQL, PL/Perl, and PL/Python. Let's rewrite an example we demonstrated in Chapter 5, PL/pgSQL Trigger Functions. Recall the simple, "Hey, I am called" trigger. This is how the PL/Tcl version of the example looks:

CREATE OR REPLACE FUNCTION notify_trigger_pltcl() RETURNS TRIGGER 
AS $$ 
  set result [format "Hi, I got %s invoked FOR %s %s %s on %s" $TG_name $TG_level $TG_when $TG_op $TG_table_name]
  if {$TG_op == "UPDATE"} { 
    append result [format " OLD = %s AND NEW=%s" $OLD(i) $NEW(i)]
      set NEW(i) [expr $OLD(i) + $NEW(i)]
      elog NOTICE $result
      return [array get NEW]
  } elseif {$TG_op == "DELETE"} {
      elog NOTICE "DELETE"
      return SKIP
    } elog NOTICE $result
  return OK
$$ LANGUAGE pltcl;

CREATE TABLE notify_test_pltcl(i int);

CREATE  TRIGGER notify_insert_pltcl_trigger
  BEFORE INSERT OR UPDATE OR DELETE ON notify_test_pltcl
  FOR EACH ROW
EXECUTE PROCEDURE notify_trigger_pltcl();

The preceding code is almost a carbon copy of the one in Chapter 11, PL/Perl – Perl Procedural Language, but in the Tcl syntax. It demonstrates how to use TG variables, how to modify NEW values, and how to SKIP an operation.

It prints "Hey I got invoked" with different attributes of the trigger. In the case of an UPDATE, it also prints the NEW and OLD values, as well as modifies the NEW value and skips the DELETE operation. A PL/Tcl trigger function can return the following values:

  • OK: This is the default value and implies that the operation executed by the user will proceed normally
  • SKIP: This return value implies that the user-executed operation will be silently ignored
  • LIST: This is returned by array get and implies that a modified version of the NEW array should be returned
  • OLD and NEW: These values are available as associative arrays in a PL/Tcl trigger function, and the attributes of the table are the names of the elements in the array

Let's run INSERT, UPDATE, and DELETE to see the results:

postgres=# INSERT INTO notify_test_pltcl VALUES(1);
NOTICE:  Hi, I got notify_insert_pltcl_trigger invoked FOR ROW BEFORE INSERT on notify_test_pltcl
INSERT 0 1
postgres=# UPDATE notify_test_pltcl SET i=10;
NOTICE:  Hi, I got notify_insert_pltcl_trigger invoked FOR ROW BEFORE UPDATE on notify_test_pltcl OLD = 1 AND NEW=10
UPDATE 1
postgres=# DELETE FROM notify_test_pltcl;
NOTICE:  DELETE
DELETE 0
postgres=# SELECT * FROM notify_test_pltcl;
 i  
----
 11
(1 row)

In a PL/Tcl trigger function, the $TG variables contain information about the current trigger event. You can see the full list of variables at http://www.postgresql.org/docs/current/interactive/pltcl-trigger.html.

The variables we have used in our example are explained in the following table:

Trigger name

Description

$TG_name

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

$TG_level

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

$TG_when

This is a BEFORE, AFTER, or INSTEAD OF trigger. In our example, this will contain BEFORE.

$TG_op

This is an INSERT, UPDATE, DELETE, or TRUNCATE trigger. In our example, this will contain INSERT, UPDATE, or DELETE.

$TG_table_name

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

$OLD(i)

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

$NEW(i)

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

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

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