Re: creating audit tables

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