Re: "OLD." || myColumnNameVar (How to generically access columns in a trigger's OLD or NEW records)

On 04/28/2011 10:46 PM, Basil Bourque wrote:

> In PL/pgSQL, how does one generically access the fields of the OLD or NEW record?
>
> I've tried code such as this:
>    'NEW.' || quote_ident( myColumnNameVar ) || '::varchar'
>
> But when run by an "EXECUTE" command, I get errors such as:
>    ERROR:  missing FROM-clause entry for table "old"
>    SQL state: 42P01
>
> It seems that I cannot get PL/pgSQL to interpret the text of "NEW." + column name as text.
>
> My goal is to loop each field in a trigger, comparing the "OLD."&  "NEW." values of each field. If different I want
tolog both values in a history/audit-trail table. 
>
> Is there some way to loop the fields of a trigger's Record? I've read other people's frustration at not being able to
getan array of fields from the Record. 
>
> My approach is to fake it: Get the table's columns and data types by querying the meta-data tables (pg_attribute,
pg_class,pg_type). But I cannot get "NEW." || colNameVar to be interpreted. Perhaps there is a better approach. 
>
> If anyone is curious, my source code is pasted below.
>
> --Basil Bourque
We use plpythonu for this as the new and old structures are dictionaries.

Sim