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. [EMAIL PROTECTED] GMOD Coordinator (http://www.gmod.org/) 216-392-3087 Cold Spring Harbor Laboratory ---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster