Re: creating audit tables

Scott Cain wrote:
> 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