The classic "NEW used in query that is not in a rule" problem again

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.

On Tue, Nov 09, 2004 at 11:00:57PM -0000, John White wrote:
> 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.

This is hard to do with plpgsql, I don't understand why people keeps trying.
It's much easier with PL/Tcl. An example, if you had these tables

CREATE TABLE usuarios
(usuario_id int,
nombre text);

CREATE TABLE usuarios_audit
(usuario_id int,
nombre text,
op text,
fecha timestamp with time zone);

You could do something like

CREATE OR REPLACE FUNCTION
audita_usuarios() RETURNS trigger AS '
spi_exec "INSERT INTO usuarios_audit
VALUES ($NEW(usuario_id),
''[ quote $NEW(nombre) ]'',
''[ quote $TG_op ]'',
now())"
return [array get NEW]
' LANGUAGE pltcl;

CREATE TRIGGER audita_usuarios
BEFORE UPDATE OR INSERT OR DELETE
ON usuarios FOR EACH ROW
EXECUTE PROCEDURE audita_usuarios();

Note that you can give parameters to the function, as illustrated
by this other example (doing case folding, but should be trivial
to understand how to modify it):

CREATE TABLE a_table (
column_1 text,
column_2 text
);

CREATE OR REPLACE FUNCTION minusculas()
RETURNS trigger AS '
foreach key $args {
if {[info exists NEW($key)]} {
set NEW($key) [string tolower $NEW($key)]
}
}
return [array get NEW]
' LANGUAGE pltcl;

CREATE TRIGGER minusculizar
BEFORE INSERT OR UPDATE ON a_table
FOR EACH ROW EXECUTE PROCEDURE
minusculas('column_1', 'column_2');

You should be able to do whatever you want to do by extending
this examples, much more easily than by using plpgsql.
No wonder your eyes were bleeding.

I think the second example is almost verbatim from Elein Mustain's
excellent General Bits column.

--
Alvaro Herrera (<alvherre[(at)]dcc(dot)uchile(dot)cl>)
"Pensar que el espectro que vemos es ilusorio no lo despoja de espanto,
sólo le suma el nuevo terror de la locura" (Perelandra, CSLewis)