Dynamic access to record columns (in PL/pgsql)

Hello

I have a trigger procedure which I want to be called after insert or
update on different tables. In this procedure, I need to find the values
of key fields for the affected table in the NEW record, but I can't find
a way to access a field the name of which I only know at execution time.
I tried many workarounds, for example this:

fieldname := name of key field;
EXECUTE 'SELECT NEW.'||fieldname INTO fieldvalue;

But none of them worked. I found a one-year-old thread about this kind
of dynamic access, which proposed a syntax like this: NEW.(fieldname),
but this hasn't be implemented in the current version and, apparently,
no different methods where suggested. Can anybody suggest me how to do
that? Right now I can only think of programmatically generating one
different procedure for every table I want to track (there are 42), but
this would be very clunky solution.

Thanks for your time

Daniele Mazzini

Daniele Mazzini <daniele(dot)mazzini(at)libero(dot)it> writes:
> I have a trigger procedure which I want to be called after insert or
> update on different tables. In this procedure, I need to find the values
> of key fields for the affected table in the NEW record, but I can't find
> a way to access a field the name of which I only know at execution time.

There is no way to do that in plpgsql. You could do it in the other PLs
(eg plperl, pltcl) since they are not as strongly typed as plpgsql.

regards, tom lane

Tom Lane wrote:
> Daniele Mazzini <daniele(dot)mazzini(at)libero(dot)it> writes:
>
>> I have a trigger procedure which I want to be called after insert or
>> update on different tables. In this procedure, I need to find the values
>> of key fields for the affected table in the NEW record, but I can't find
>> a way to access a field the name of which I only know at execution time.
>>
>
> There is no way to do that in plpgsql. You could do it in the other PLs
> (eg plperl, pltcl) since they are not as strongly typed as plpgsql.
>
> regards, tom lane
>
>
Well, I wouldn't go that far. A little more information might point to
a solution here. Given any one table that this trigger might fire on,
are the fields that you need to check defined? I.e., if the trigger
fires on table A, will you always be looking at column A.b and on table
B column B.a? If so, when you create the trigger, pass in some kind of
variable that the trigger can use to know what field it's looking for.
If that doesn't make enough sense, let me know and I can give you a more
fleshed out example of what I'm talking about.

--
erik jones <erik(at)myemma(dot)com>
software development
emma(r)