[GENERAL] 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.                                         [EMAIL PROTECTED]
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