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/ -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
