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