Re: creating audit tables - Mailing list pgsql-general

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