Re: creating audit tables

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(at)cshl(dot)org
GMOD Coordinator (http://www.gmod.org/) 216-392-3087
Cold Spring Harbor Laboratory