Heck! So much for feeling close. It is somewhat frustrating to me that
such an obviously useful tool (having and using audit tables) should be
so difficult to implement. I thought I had a reasonable chance of doing
it in plpgsql because I've written functions in that before--I have no
idea how to do it in tkl.
If someone would show me a simple example for doing this for one table,
I will happily make available the script I am writing that will generate
audit tables and the functions and triggers for using them
automatically, given any ddl file. It is based on the Perl module
SQL::Translator.
Thanks,
Scott
On Thu, 2004-10-14 at 14:07, Richard Huxton wrote:
> 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.
--
------------------------------------------------------------------------
Scott Cain, Ph. D. cain@cshl.org
GMOD Coordinator (http://www.gmod.org/) 216-392-3087
Cold Spring Harbor Laboratory
