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. ---------------------------(end of broadcast)--------------------------- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
