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();