Re: creating audit tables

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;

Thanks again,
Scott

On Thu, 2004-10-14 at 11:16, Ian Harding wrote:
> I think you want to EXECUTE that sql so it doesn't get compiled into the
> function.
>
> http://www.postgresql.org/docs/7.4/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN
>
> - Ian
>
> >>> Scott Cain <cain(at)cshl(dot)org> 10/14/04 8:01 AM >>>
> OK, I've reworked my function and I can now create my functions and
> triggers; however, when I try to do a test update, I get the following
> error:
>
> ERROR: syntax error at or near "$1" at character 14
> CONTEXT: PL/pgSQL function "audit_update" line 7 at SQL statement
>
> Which I think corresponds to 'audit_table' in the INSERT line below:
>
> CREATE FUNCTION audit_update() RETURNS trigger
> AS '
> DECLARE
> audit_table text;
> table_name text;
> BEGIN
> table_name = TG_RELNAME;
> audit_table = ''audit_'' || table_name;
> INSERT INTO audit_table VALUES (SELECT OLD.*,now(),''U'' FROM
> table_name);
> return NEW;
> END
> '
> LANGUAGE plpgsql;
>
> I am trying to dynamically construct the audit table's name from the
> TG_RELNAME variable (the audit table is always named as the name of the
> original table with 'audit_' prepended to it). Is this not a valid
> thing to do?
>
> Thanks,
> Scott
>
> On Wed, 2004-10-13 at 23:59, Tom Lane wrote:
> > Scott Cain <cain(at)cshl(dot)org> writes:
> > > I am trying to create audit tables for all of the tables in my
> > > database. The function, table and trigger create statements are
> below.
> > > Apparently, I am not doing it quite right, because I get these
> messages
> > > when I try to run the create statements below:
> >
> > Trigger functions don't take any explicit parameters. Everything they
> > need they get through specialized mechanisms (in plpgsql, it's special
> > variables like tgargv).
> >
> > regards, tom lane
> >
> > ---------------------------(end of
> broadcast)---------------------------
> > TIP 4: Don't 'kill -9' the postmaster
--
------------------------------------------------------------------------
Scott Cain, Ph. D. cain(at)cshl(dot)org
GMOD Coordinator (http://www.gmod.org/) 216-392-3087
Cold Spring Harbor Laboratory