PLRuby::Description::Trigger (Class)

In: plruby.rb
Parent: Object

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]].to_i + 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();

[Validate]