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

[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(at)pgpeng(dot)com
> <mailto:vmaverick(at)pgpeng(dot)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(at)une(dot)edu(dot)au