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

[Alvaro Herrera <alvherre@dcc.uchile.cl>]

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[@]dcc.uchile.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)

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
      subscribe-nomail command to majordomo@postgresql.org so that your
      message can get through to the mailing list cleanly