creating audit tables - Mailing list pgsql-general

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