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

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

From: hubert depesz lubaczewski <depesz(at)depesz(dot)com>
To: Basil Bourque <basil(dot)list(at)me(dot)com>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: "OLD." || myColumnNameVar (How to generically access columns in a trigger's OLD or NEW records)
Date: 2011-04-28 19:57:10
Message-ID: 20110428195710.GA21677@depesz.com (view raw or flat)
Thread:
Lists: pgsql-general
On Thu, Apr 28, 2011 at 12:46:50PM -0700, 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 to log 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 get an 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.

long story short - it's not possible.

a bit longer story:
there are couple of workarounds.

1. you can use hstore datatype and it's casts from record to hstore
2. you can use another pl/* language - like pl/perl - which doesn't have
this problem
3. you can use ready made tool for auditing that does what you want, so
you don't have to worry ( http://pgfoundry.org/projects/tablelog/ )
4. http://www.depesz.com/index.php/2010/03/10/dynamic-updates-of-fields-in-new-in-plpgsql/
but really, read, and understand the warnings.

depesz

-- 
The best thing about modern society is how easy it is to avoid contact with it.
                                                             http://depesz.com/

In response to

pgsql-general by date

Next:From: John DeSoiDate: 2011-04-28 20:08:26
Subject: Re: "OLD." || myColumnNameVar (How to generically access columns in a trigger's OLD or NEW records)
Previous:From: Basil BourqueDate: 2011-04-28 19:46:50
Subject: "OLD." || myColumnNameVar (How to generically access columns in a trigger's OLD or NEW records)

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