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

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)