Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-general by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group