creating audit tables

[Scott Cain]

Hello,

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:

CREATE FUNCTION
CREATE FUNCTION
CREATE TABLE
CREATE TABLE
GRANT
ERROR: function audit_update() does not exist
ERROR: function audit_delete() does not exist

Why do I get a message that the functions don't exist when they were
just successfully created?

Thanks much,
Scott

Here's the ddl:
CREATE FUNCTION audit_update(varchar) RETURNS trigger
AS '
DECLARE
audit_table varchar;
table_name varchar;
BEGIN
table_name = $1;
audit_table = ''audit_'' || table_name;
INSERT INTO audit_table VALUES (SELECT OLD.*,now(),''U'' FROM table_name);
return NEW;
END
'
LANGUAGE plpgsql;

CREATE FUNCTION audit_delete(varchar) RETURNS trigger
AS '
DECLARE
audit_table varchar;
table_name varchar;
BEGIN
table_name = $1;
audit_table = ''audit_'' || table_name;
INSERT INTO audit_table VALUES (SELECT OLD.*,now(),''D'' FROM table_name);
return OLD;
END
'
LANGUAGE plpgsql;

create table tableinfo (
tableinfo_id serial not null,
primary key (tableinfo_id),
name varchar(30) not null,
primary_key_column varchar(30) null,
is_view int not null default 0,
view_on_table_id int null,
superclass_table_id int null,
is_updateable int not null default 1,
modification_date date not null default now(),
constraint tableinfo_c1 unique (name)
);

CREATE TABLE audit_tableinfo (
tableinfo_id integer,
name varchar,
primary_key_column varchar,
is_view integer,
view_on_table_id integer,
superclass_table_id integer,
is_updateable integer,
modification_date date,
transaction_date timestamp not null,
transaction_type char not null
);
GRANT ALL on audit_tableinfo to PUBLIC;

CREATE TRIGGER tableinfo_audit_u
BEFORE UPDATE ON tableinfo
FOR EACH ROW
EXECUTE PROCEDURE audit_update('tableinfo');

CREATE TRIGGER tableinfo_audit_d
BEFORE DELETE ON tableinfo
FOR EACH ROW
EXECUTE PROCEDURE audit_delete('tableinfo');
--
------------------------------------------------------------------------
Scott Cain, Ph. D. ca**@cshl.org
GMOD Coordinator (http://www.gmod.org/) 216-392-3087
Cold Spring Harbor Laboratory
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

[Tom Lane]

Scott Cain <ca**@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]

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 <ca**@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. ca**@cshl.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