Re: triggers: dynamic references to fields in NEW and OLD?

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(at)une(dot)edu(dot)au]
Sent: Thursday, May 15, 2008 8:06 PM
To: Vance Maverick
Cc: pgsql-general(at)postgresql(dot)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(at)une(dot)edu(dot)au