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 (<[EMAIL PROTECTED]>)
"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 [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
