Re: creating audit tables

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

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings