On Tue, Nov 09, 2004 at 11:00:57PM -0000, John White wrote:
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 (AT) postgresql (DOT) org so that your
message can get through to the mailing list cleanly