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@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