It seems I'm not the first to ask this question but there seem to be
very few answers. I am implementing an audit log facility where INSERT's
or UPDATE's to a number tables get logged to a single table. Each row in
the logging table stores data on one field change in the tables being
logged. My function is available here
http://rafb.net/paste/results/JwQeqj35.html. Basically a SQL string is
generated for each field in the inserted record, this SQL refres to
"new" but when it's executed I get "NEW used in query that is not in a
rule
Here's the bit of SQL that builds the string
(Beware all the single quoting - my eyes have only stopped bleeding)
-- start
FOR recFields in SELECT attname FROM pg_attribute WHERE attrelid =
TG_RELID AND attnum >= 0 loop
auditfieldid =
nextval(\'seq_tbl_auditfields_fld_auditfieldid\'::text);
SQL := \'INSERT INTO tbl_auditfields(fld_auditfieldid,
fld_audittableid, fld_fieldname, fld_newdata) VALUES (\';
SQL := SQL || auditfieldid::text || \', \' ||
audittableid::text || \', \';
SQL := SQL || '''''''' || recFields.attname || ''''''''
|| \', new.\' || recFields.attname || \'::text);\';
raise NOTICE ''SQL = %'', SQL;
EXECUTE SQL;
END LOOP;
-- end
and here's a sample string that's generated (as reported by the RAISE
NOTICE) and it looks ok.
INSERT INTO tbl_auditfields(fld_auditfieldid, fld_audittableid,
fld_fieldname, fld_newdata)
VALUES (65, 11, 'fld_uid', new.fld_uid::text);
When I execute this I get the error above. Also the docs for the RENAME
command seem to hint that you can get around this problem by renaming
'new', but rename is broken right now, and is low priority for fixing.
If I could even evaluate the new.<whatever> outside the SQL and put it's
value in instead, I'd be happy. Any help would be much appreciated (I
really don't want to have to automatically generate a trigger function
for each table).
Cheers,
John.
