Re: A generic trigger? - Mailing list pgsql-sql

On Sunday 14 September 2003 02:13, ow wrote:
> Hi,
>
> Am looking for a way to minimize the amount of fuctions that support
> triggers. E.g., there's "company" and "company_backup" tables. Update
> trigger on the "company" table will put a record in the "company_backup"
> table whenever "company" record is updated.
>
> The problem is that there's quite a few other tables for which similar
> backup logic has to be done (e.g. "custormer" and "customer_backup", etc).
> The backup logic is the same, only structure of the tables changes.
>
> Is there a way to write a generic trigger/function that would deal with
> backup regardless of the table structure?
>
> Thanks in advance.
>
>
>
Yes it is possible and I've done it. The reason I'm not using it is because I 
wrote it in Pl/Python and if you attach the same trigger to more than one 
table in the same transaction pg/python (actually the entire server crashes 
but thats not the point) crashes. Well it did when I last tested it in early 
versions. I'm still thinking of getting around to rewriting it in a language 
without this bug, since nobody sounds like they are going to fix it. C might 
be best!This version inserts all the history in the same table. But since its broke 
anyway changing it to insert into different tables should not be too 
difficult. There are some scripting languages where somthing don't work hense why I 
chose pl/python.... The trigger/function is below although it should be in 
the archives somwhere as well. Full problem with it can be seen of Bugs....

Peter Childs



-- CREATE TABLE history ( tab      text                        field    text                        
action   text                        before   text                        
after    text                        occured  timestamp without time zone key      
text who      text  );

DROP INDEX history_tab;
DROP INDEX history_tab_field;
DROP INDEX history_tab_key;
DROP INDEX history_tab_who;
DROP INDEX history_who;
CREATE INDEX history_tab on history(tab);
CREATE INDEX history_tab_field on history(tab,field);
CREATE INDEX history_tab_key on history(tab,key);
CREATE INDEX history_tab_who on history(tab,who);
CREATE INDEX history_who on history(who);

CREATE OR REPLACE FUNCTION history_update() RETURNS TRIGGER AS '
if TD["event"] == "INSERT": lookup = "new"
elif TD["event"] == "DELETE": lookup = "old"
else: lookup = "new"
p = plpy.execute(" SELECT CASE i.indproc WHEN (''-''::pg_catalog.regproc) THEN 
a.attname ELSE SUBSTR(pg_catalog.pg_get_indexdef(attrelid), POSITION(''('' in 
pg_catalog.pg_get_indexdef(attrelid))) END as pkey, a.atttypid::int, 
c2.relname FROM pg_catalog.pg_class c, pg_catalog.pg_class c2, 
pg_catalog.pg_index i, pg_catalog.pg_attribute a WHERE c.oid = " + 
TD["relid"] + " AND c.oid = i.indrelid AND i.indexrelid = c2.oid and 
a.attrelid = i.indexrelid and NOT a.attisdropped and i.indisprimary ORDER BY 
i.indisprimary DESC, i.indisunique DESC, c2.relname;")
if len(p) > 0: pkey = TD[lookup][p[0]["pkey"]] ppkey = p[0]["pkey"]
else: pkey = "" ppkey = ""
rel = plpy.execute("select relname from pg_class where oid=" + TD["relid"] + 
";")
relname = rel[0]["relname"]
plan = plpy.prepare("INSERT INTO history 
(tab,field,action,before,after,occured,who,key) values 
($1,$2,$3,$4,$5,now(),user,$6);",["text","text","text","text","text","text"])
if TD["event"] == "INSERT": old = "" new = pkey plpy.execute(plan,[relname,ppkey,TD["event"],old,new,pkey])
else: for key in TD[lookup].keys():   dont = 0   if TD["event"] == "INSERT":     old = ""     new = TD["new"][key]
ifnew == None:       dont = 1   elif TD["event"] == "UPDATE":     old = TD["old"][key]     new = TD["new"][key]   else:
   old = TD["old"][key]     new = ""   if old == None:     old = "Null"   if new == None:     new = "Null"   if new ==
old:    dont = 1   if not(dont):     plpy.execute(plan,[relname,key,TD["event"],old,new,pkey])
 
' LANGUAGE 'plpython';

CREATE TRIGGER history_update AFTER INSERT OR UPDATE OR DELETE ON  
account_history      
FOR EACH ROW EXECUTE PROCEDURE history_update();