Dynamic access to record columns (in PL/pgsql)

From: Daniele Mazzini <daniele(dot)mazzini(at)libero(dot)it>
To: pgsql-general(at)postgresql(dot)org
Subject: Dynamic access to record columns (in PL/pgsql)
Date: 2006-09-30 10:51:07
Message-ID: 451E4C1B.2010201@libero.it
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Bruce Momjian 2006-09-30 11:24:45 Re: PG Rocks! (was:Backslashes in 8.2 still escape,
Previous Message Andreas Rieke 2006-09-30 08:02:02 Re: memory issues when running with mod_perl