class PLRuby::Description::Trigger

Trigger procedures are defined in Postgres as functions without arguments and a return type of trigger. In PLRuby the procedure is called with 4 arguments :

• new (hash, tainted)

  an hash containing the values of the new table row on INSERT/UPDATE actions, or empty on DELETE.

• old (hash, tainted)

  an hash containing the values of the old table row on UPDATE/DELETE actions, or empty on INSERT

• args (array, tainted, frozen)

  An array of the arguments to the procedure as given in the CREATE TRIGGER statement

• tg (hash, tainted, frozen)

  The following keys are defined

  • name

    The name of the trigger from the CREATE TRIGGER statement.

  • relname

    The name of the relation who has fired the trigger

  • relid

    The object ID of the table that caused the trigger procedure to be invoked.

  • relatts

    An array containing the name of the tables field.

  • when

    The constant PL::BEFORE, PL::AFTER or PL::UNKNOWN depending on the event of the trigger call.

  • level

    The constant PL::ROW or PL::STATEMENT depending on the event of the trigger call.

  • op

    The constant PL::INSERT, PL::UPDATE or PL::DELETE depending on the event of the trigger call.

The return value from a trigger procedure is one of the constant PL::OK or PL::SKIP, or an hash. If the return value is PL::OK, the normal operation (INSERT/UPDATE/DELETE) that fired this trigger will take place. Obviously, PL::SKIP tells the trigger manager to silently suppress the operation. The hash tells PLRuby to return a modified row to the trigger manager that will be inserted instead of the one given in new (INSERT/UPDATE only). Needless to say that all this is only meaningful when the trigger is BEFORE and FOR EACH ROW.

Here's a little example trigger procedure that forces an integer value in a table to keep track of the # of updates that are performed on the row. For new row's inserted, the value is initialized to 0 and then incremented on every update operation :

CREATE FUNCTION trigfunc_modcount() RETURNS TRIGGER AS '
    case tg["op"]
    when PL::INSERT
        new[args[0]] = 0
      when PL::UPDATE
          new[args[0]] = old[args[0]] + 1
      else
          return PL::OK
      end
      new
  ' LANGUAGE 'plruby';

  CREATE TABLE mytab (num int4, modcnt int4, descr text);

  CREATE TRIGGER trig_mytab_modcount BEFORE INSERT OR UPDATE ON mytab
      FOR EACH ROW EXECUTE PROCEDURE trigfunc_modcount('modcnt');

A more complex example (extract from test_setup.sql in the distribution) which use the global variable $Plans to store a prepared plan

create function trig_pkey2_after() returns trigger as '
   if ! $Plans.key?("plan_dta2_upd")
       $Plans["plan_dta2_upd"] = 
            PL::Plan.new("update T_dta2 
                          set ref1 = $3, ref2 = $4
                          where ref1 = $1 and ref2 = $2",
                         ["int4", "varchar", "int4", "varchar" ]).save
       $Plans["plan_dta2_del"] = 
            PL::Plan.new("delete from T_dta2 
                          where ref1 = $1 and ref2 = $2", 
                         ["int4", "varchar"]).save
   end

   old_ref_follow = false
   old_ref_delete = false

   case tg["op"]
   when PL::UPDATE
       new["key2"] = new["key2"].upcase
       old_ref_follow = (new["key1"] != old["key1"]) || 
                        (new["key2"] != old["key2"])
   when PL::DELETE
       old_ref_delete = true
   end

   if old_ref_follow
       n = $Plans["plan_dta2_upd"].exec([old["key1"], old["key2"], new["key1"],
new["key2"]])
       warn "updated #{n} entries in T_dta2 for new key in T_pkey2" if n != 0
   end

   if old_ref_delete
       n = $Plans["plan_dta2_del"].exec([old["key1"], old["key2"]])
       warn "deleted #{n} entries from T_dta2" if n != 0
   end

   PL::OK
' language 'plruby';

create trigger pkey2_after after update or delete on T_pkey2
 for each row execute procedure
 trig_pkey2_after();