Thread: triggers: dynamic references to fields in NEW and OLD?
I have a bunch of tables that are similar in some ways, and I'm about to put triggers on them. The triggers will all do essentially the same thing -- the only wrinkle is that the name of the column they operate on varies from table to table. I'd like to have just one trigger function, written 'dynamically' so it can take the name of the column as a trigger parameter (in TG_ARGV). For example, given tables CREATE TABLE a (aa INT); CREATE TABLE b (bb INT); I'd like to be able to write a trigger function foo() such that with trigger declarations CREATE TRIGGER a_foo AFTER INSERT OR UPDATE OR DELETE ON a FOR EACH ROW EXECUTE PROCEDURE foo('aa'); CREATE TRIGGER b_foo AFTER INSERT OR UPDATE OR DELETE ON b FOR EACH ROW EXECUTE PROCEDURE foo('bb'); the logic in foo() reads columns a.aa or b.bb respectively. I've tried composing a SQL string including the text 'NEW.aa' or 'NEW.bb' appropriately, and then passing this to EXECUTE. This fails: ERROR: NEW used in query that is not in a rule Any suggestions? If this can't be made to work, I'll probably write a function that dynamically generates the code with the desired substitutions -- so in the SQL source I'll have SELECT create_custom_trigger('a', 'aa'); generating both the trigger function and the trigger. Vance
Vance Maverick wrote: > I have a bunch of tables that are similar in some ways, and I'm about to > put triggers on them. The triggers will all do essentially the same > thing -- the only wrinkle is that the name of the column they operate on > varies from table to table. I'd like to have just one trigger function, > written 'dynamically' so it can take the name of the column as a trigger > parameter (in TG_ARGV). For example, given tables > > CREATE TABLE a (aa INT); > CREATE TABLE b (bb INT); > > I'd like to be able to write a trigger function foo() such that with > trigger declarations > > CREATE TRIGGER a_foo AFTER INSERT OR UPDATE OR DELETE ON a > FOR EACH ROW EXECUTE PROCEDURE foo('aa'); > CREATE TRIGGER b_foo AFTER INSERT OR UPDATE OR DELETE ON b > FOR EACH ROW EXECUTE PROCEDURE foo('bb'); > > the logic in foo() reads columns a.aa or b.bb respectively. > > I've tried composing a SQL string including the text 'NEW.aa' or > 'NEW.bb' appropriately, and then passing this to EXECUTE. This fails: > > ERROR: NEW used in query that is not in a rule > > Any suggestions? > If you just need which table triggered the function then |TG_TABLE_NAME| may be simpler than passing parameters. Something like this will probably work for you (replace the raise notice with whatever you have to do) create or replace function atest() returns trigger as $$ declare avalue int; tblfld text; begin tblfld := tg_argv[0]; if tblfld = 'aa' then avalue := new.aa; else if tblfld = 'bb' then avalue := new.bb; end if; end if; raise notice '%',avalue; return new; end; $$ language plpgsql; klint. -- Klint Gore Database Manager Sheep CRC A.G.B.U. University of New England Armidale NSW 2350 Ph: 02 6773 3789 Fax: 02 6773 3266 EMail: kgore4@une.edu.au
Thanks, this does work. Unfortunately it requires the trigger function to iterate through all the possible column names explicitly. (I have about 10, and the number might grow in the future.) Vance -----Original Message----- From: Klint Gore [mailto:kgore4@une.edu.au] Sent: Thursday, May 15, 2008 8:06 PM To: Vance Maverick Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] triggers: dynamic references to fields in NEW and OLD? Vance Maverick wrote: > I have a bunch of tables that are similar in some ways, and I'm about > to put triggers on them. The triggers will all do essentially the > same thing -- the only wrinkle is that the name of the column they > operate on varies from table to table. I'd like to have just one > trigger function, written 'dynamically' so it can take the name of the > column as a trigger parameter (in TG_ARGV). For example, given tables > > CREATE TABLE a (aa INT); > CREATE TABLE b (bb INT); > > I'd like to be able to write a trigger function foo() such that with > trigger declarations > > CREATE TRIGGER a_foo AFTER INSERT OR UPDATE OR DELETE ON a > FOR EACH ROW EXECUTE PROCEDURE foo('aa'); > CREATE TRIGGER b_foo AFTER INSERT OR UPDATE OR DELETE ON b > FOR EACH ROW EXECUTE PROCEDURE foo('bb'); > > the logic in foo() reads columns a.aa or b.bb respectively. > > I've tried composing a SQL string including the text 'NEW.aa' or > 'NEW.bb' appropriately, and then passing this to EXECUTE. This fails: > > ERROR: NEW used in query that is not in a rule > > Any suggestions? > If you just need which table triggered the function then |TG_TABLE_NAME| may be simpler than passing parameters. Something like this will probably work for you (replace the raise notice with whatever you have to do) create or replace function atest() returns trigger as $$ declare avalue int; tblfld text; begin tblfld := tg_argv[0]; if tblfld = 'aa' then avalue := new.aa; else if tblfld = 'bb' then avalue := new.bb; end if; end if; raise notice '%',avalue; return new; end; $$ language plpgsql; klint. -- Klint Gore Database Manager Sheep CRC A.G.B.U. University of New England Armidale NSW 2350 Ph: 02 6773 3789 Fax: 02 6773 3266 EMail: kgore4@une.edu.au
Thanks! Your solution clearly works, but it requires the shared function to enumerate all possible column names. In my real case, there are 8-10 distinct names, so that's a bit ugly....but it works. Vance -----Original Message----- If you just need which table triggered the function then |TG_TABLE_NAME| may be simpler than passing parameters. Something like this will probably work for you (replace the raise notice with whatever you have to do) create or replace function atest() returns trigger as $$ declare avalue int; tblfld text; begin tblfld := tg_argv[0]; if tblfld = 'aa' then avalue := new.aa; else if tblfld = 'bb' then avalue := new.bb; end if; end if; raise notice '%',avalue; return new; end; $$ language plpgsql; klint. -- Klint Gore Database Manager Sheep CRC A.G.B.U. University of New England Armidale NSW 2350 Ph: 02 6773 3789 Fax: 02 6773 3266 EMail: kgore4@une.edu.au
Vance,
I missed your earlier post, so I may be misunderstanding the situation, but I think you could do this more easily in plpython, because TD['new'] and TD['old'] are dictionaries, and you can traverse the dictionaries like this:
for k, v in TD['new'].items():
if tblfld == k:
plpy.notice('%s' % v)
This probably looks like gibberish if you're not used to python, but if you'd like more help, email me back (with your original post) and I'll get back to you next week.
Kerri
--
Yuma Educational Computer Consortium
Compass Development Team
Kerri Reno
kreno@yumaed.org (928) 502-4240
.·:*¨¨*:·. .·:*¨¨*:·. .·:*¨¨*:·.
I missed your earlier post, so I may be misunderstanding the situation, but I think you could do this more easily in plpython, because TD['new'] and TD['old'] are dictionaries, and you can traverse the dictionaries like this:
for k, v in TD['new'].items():
if tblfld == k:
plpy.notice('%s' % v)
This probably looks like gibberish if you're not used to python, but if you'd like more help, email me back (with your original post) and I'll get back to you next week.
Kerri
On 5/15/08, Vance Maverick <vmaverick@pgpeng.com> wrote:
Thanks! Your solution clearly works, but it requires the shared function to
enumerate all possible column names. In my real case, there are 8-10
distinct names, so that's a bit ugly....but it works.
Vance
-----Original Message-----
If you just need which table triggered the function then |TG_TABLE_NAME| may
be simpler than passing parameters.
Something like this will probably work for you (replace the raise notice
with whatever you have to do)
create or replace function atest() returns trigger as $$ declare
avalue int;
tblfld text;
begin
tblfld := tg_argv[0];
if tblfld = 'aa' then
avalue := new.aa;
else
if tblfld = 'bb' then
avalue := new.bb;
end if;
end if;
raise notice '%',avalue;
return new;
end;
$$ language plpgsql;
klint.
--
Klint Gore
Database Manager
Sheep CRC
A.G.B.U.
University of New England
Armidale NSW 2350
Ph: 02 6773 3789
Fax: 02 6773 3266
EMail: kgore4@une.edu.au
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
--
Yuma Educational Computer Consortium
Compass Development Team
Kerri Reno
kreno@yumaed.org (928) 502-4240
.·:*¨¨*:·. .·:*¨¨*:·. .·:*¨¨*:·.
[see below] Kerri Reno wrote: > Vance, > > I missed your earlier post, so I may be misunderstanding the > situation, but I think you could do this more easily in plpython, > because TD['new'] and TD['old'] are dictionaries, and you can traverse > the dictionaries like this: > > for k, v in TD['new'].items(): > if tblfld == k: > plpy.notice('%s' % v) > > This probably looks like gibberish if you're not used to python, but > if you'd like more help, email me back (with your original post) and > I'll get back to you next week. > > Kerri > > On 5/15/08, *Vance Maverick* <vmaverick@pgpeng.com > <mailto:vmaverick@pgpeng.com>> wrote: > > Thanks! Your solution clearly works, but it requires the shared > function to > enumerate all possible column names. In my real case, there are 8-10 > distinct names, so that's a bit ugly....but it works. > > Vance > > -----Original Message----- > If you just need which table triggered the function then > |TG_TABLE_NAME| may > be simpler than passing parameters. > > Something like this will probably work for you (replace the raise > notice > with whatever you have to do) > > create or replace function atest() returns trigger as $$ declare > avalue int; > tblfld text; > begin > tblfld := tg_argv[0]; > if tblfld = 'aa' then > avalue := new.aa; > else > if tblfld = 'bb' then > avalue := new.bb <http://new.bb>; > end if; > end if; > raise notice '%',avalue; > return new; > end; > $$ language plpgsql; > > klint. > Agree with Kerri - do it in one of the languages other than plpgsql. Plpgsql can't do the for loop as simply as other languages. There's no way to walk a record structure (new) as a collection/array and pull out the item you are interested in. You could possibly cheat by putting new into a temp table and then executing a select on it. Performance will probably be bad. create temp table newblah as select new.*; execute 'select new. ' || tg_argv[0] || '::text' || ' from newblah new ' into newval; execute 'drop table newblah'; There probably is a function in the plpgsql internals that will pull a named field out of a record but I have no idea what it is or if it's exposed so that it can be called. Maybe someone who knows about the internals of plpgsql could comment - is there a function like getfieldfromrecord(record,text)? klint. -- Klint Gore Database Manager Sheep CRC A.G.B.U. University of New England Armidale NSW 2350 Ph: 02 6773 3789 Fax: 02 6773 3266 EMail: kgore4@une.edu.au