I feel like I am getting very close, but I am still not quite there. I rewrote the trigger function below to use execute, but now I get the following error:
ERROR: OLD used in query that is not in a rule CONTEXT: PL/pgSQL function "audit_update" line 5 at execute statement
It seems that I am not able to use OLD in this context, but that is exactly what I need to do, to get the contents of the old row in the original table to put it in the audit table. Here is the function now:
CREATE FUNCTION audit_update() RETURNS trigger AS ' DECLARE audit_table text; BEGIN audit_table = ''audit_''||TG_RELNAME; EXECUTE ''INSERT INTO '' ||quote_ident(audit_table) ||'' VALUES ('' ||OLD.* ||'','' ||now() ||'',''''U'''')''; return NEW; END ' LANGUAGE plpgsql;
Looks like people were fixing your errors, not looking at what you were trying to do. Apologies, but it's easy to fixate on an error message.
Unless something is changing in 8.0 you're using the wrong tool for the job here. Plpgsql isn't good at dynamic queries, and can't unwrap OLD for you. Try a different language - tcl would be an obvious choice.
-- Richard Huxton Archonet Ltd
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
