Re: [SQL] A generic trigger?
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 textfieldtext
action textbefore text
aftertextoccured 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]
if new == 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();
---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
Re: [SQL] A generic trigger?
--- Peter Childs [EMAIL PROTECTED] wrote: 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! [snip] Hi, In my case, company and company_backup tables have the *same* structure, so I was hoping for a simpler solution using just plpgsql. Any ideas? Thanks __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] A generic trigger?
Peter Childs [EMAIL PROTECTED] writes:
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've been expecting someone to submit a fix for this, but nobody did
:-(. So I went ahead and repaired it in CVS tip. The patch is attached
if you want to try patching your local copy (it looks like it will apply
to 7.3 branch with some fuzz, but I have not actually tested it there).
regards, tom lane
*** src/pl/plpython/plpython.c.orig Mon Aug 4 14:40:50 2003
--- src/pl/plpython/plpython.c Sun Sep 14 13:07:02 2003
***
*** 224,236
static PyObject *PLy_procedure_call(PLyProcedure *, char *, PyObject *);
! /* returns a cached PLyProcedure, or creates, stores and returns
! * a new PLyProcedure.
! */
! static PLyProcedure *PLy_procedure_get(FunctionCallInfo fcinfo, bool);
static PLyProcedure *PLy_procedure_create(FunctionCallInfo fcinfo,
!bool is_trigger,
HeapTuple procTup, char *key);
static void PLy_procedure_compile(PLyProcedure *, const char *);
--- 224,234
static PyObject *PLy_procedure_call(PLyProcedure *, char *, PyObject *);
! static PLyProcedure *PLy_procedure_get(FunctionCallInfo fcinfo,
! Oid
tgreloid);
static PLyProcedure *PLy_procedure_create(FunctionCallInfo fcinfo,
!Oid tgreloid,
HeapTuple procTup, char *key);
static void PLy_procedure_compile(PLyProcedure *, const char *);
***
*** 326,332
{
DECLARE_EXC();
Datum retval;
- volatile bool is_trigger;
PLyProcedure *volatile proc = NULL;
enter();
--- 324,329
***
*** 337,343
elog(ERROR, could not connect to SPI manager);
CALL_LEVEL_INC();
- is_trigger = CALLED_AS_TRIGGER(fcinfo);
SAVE_EXC();
if (TRAP_EXC())
--- 334,339
***
*** 364,379
* PLy_restart_in_progress);
*/
! proc = PLy_procedure_get(fcinfo, is_trigger);
!
! if (is_trigger)
{
! HeapTuple trv = PLy_trigger_handler(fcinfo, proc);
retval = PointerGetDatum(trv);
}
else
retval = PLy_function_handler(fcinfo, proc);
CALL_LEVEL_DEC();
RESTORE_EXC();
--- 360,380
* PLy_restart_in_progress);
*/
! if (CALLED_AS_TRIGGER(fcinfo))
{
! TriggerData *tdata = (TriggerData *) fcinfo-context;
! HeapTuple trv;
+ proc = PLy_procedure_get(fcinfo,
+
RelationGetRelid(tdata-tg_relation));
+ trv = PLy_trigger_handler(fcinfo, proc);
retval = PointerGetDatum(trv);
}
else
+ {
+ proc = PLy_procedure_get(fcinfo, InvalidOid);
retval = PLy_function_handler(fcinfo, proc);
+ }
CALL_LEVEL_DEC();
RESTORE_EXC();
***
*** 962,971
}
! /* PLyProcedure functions
*/
static PLyProcedure *
! PLy_procedure_get(FunctionCallInfo fcinfo, bool is_trigger)
{
Oid fn_oid;
HeapTuple procTup;
--- 963,979
}
! /*
! * PLyProcedure functions
! */
!
! /* PLy_procedure_get: returns a cached PLyProcedure, or creates, stores and
! * returns a new PLyProcedure. fcinfo is the call info, tgreloid is the
! * relation OID when calling a trigger, or InvalidOid (zero) for ordinary
! * function calls.
*/
static PLyProcedure *
! PLy_procedure_get(FunctionCallInfo fcinfo, Oid tgreloid)
{
Oid fn_oid;
HeapTuple procTup;
***
*** 983,991
if (!HeapTupleIsValid(procTup))
elog(ERROR, cache lookup failed for function %u, fn_oid);
! rv = snprintf(key, sizeof(key), %u%s,
! fn_oid,
! is_trigger ? _trigger : );
if ((rv = sizeof(key)) || (rv 0))
elog(ERROR, key too long);
--- 991,997
if (!HeapTupleIsValid(procTup))
elog(ERROR, cache lookup failed for function %u, fn_oid);
! rv = snprintf(key, sizeof(key), %u_%u, fn_oid, tgreloid);
if ((rv = sizeof(key)) || (rv 0))
elog(ERROR, key too long);
***
*** 1012,1018
}
if (proc == NULL)
! proc =
