Triggers, functions and column names: a poser - Mailing list pgsql-general

here's a nice trigger problem for the weekend ;-)

I have a database (7.1.3) with a number of tables, to some of which
I wish to apply some form of logging / auditing.
(The reasons for this are manifold and related to the
business logic of the organisation which owns the database).

Specifically, for certain tables on INSERT, UPDATE or DELETE I
want to write the following to a seperate logging table:
the name of the table (relation); the action performed;
the primary key of the row affected; and a timestamp.

Rather than create a seperate RULE for each action on
each table (pain to maintain) I would like to create a
PL/PgSQL function to be called by triggers for the relevant tables.


This is the function:

    CREATE FUNCTION update_log()
      RETURNS opaque
      AS '
        DECLARE
          qid INTEGER;
        BEGIN
          IF TG_OP = ''DELETE''
            THEN qid := OLD.p_id;
            ELSE qid := NEW.p_id;
          END IF;
          INSERT INTO change_log
            (row_operation, table_name, id, created)
            VALUES(TG_OP, TG_RELNAME, qid, now());
          RETURN new;
      END;'
    LANGUAGE 'plpgsql';


This is the table 'change_log':

    CREATE TABLE change_log (
      id            integer,
      table_name    varchar(32),
      row_operation varchar(7),
      created       timestamp,
      CHECK (row_operation in ('INSERT','UPDATE','DELETE'))
    )


For a (simplified) table like this:

    CREATE TABLE product (
      p_id          SERIAL PRIMARY KEY,
      p_name        varchar(64)
    )

I would create the following trigger:

    CREATE TRIGGER trigger_product_log
      AFTER INSERT OR UPDATE OR DELETE
      ON product
      FOR EACH ROW
      EXECUTE PROCEDURE update_log();


So far so good. Unfortunately each table's primary
key is labelled differently, i.e. _not_ 'id'. I would
therefore like to find a way to determine the value
written into change_log.id without having to hard-wire
the row name into the function.

(I could of course rebuild the database with all
 relevant primary keys  renamed as 'id', and
 rewrite the overlying application, but I think I'd
 rather open up that large writhing can of worms in
 the corner now past its open-by-date ;-)

I'd guess there are three approaches to doing this:

1) provide the id from the trigger, e.g. something
   like
       CREATE TRIGGER trigger_product_log
         (...)
         EXECUTE PROCEDURE update_log(p_id);

   so that update_log finds the id in TG_ARGV[0];

2) provide the column name from the trigger, e.g.
       CREATE TRIGGER trigger_product_log
         (...)
         EXECUTE PROCEDURE update_log('p_id')

   so that update_log can do something like this:
        (...)
        DECLARE
          qid INTEGER;
        BEGIN
          qid := NEW.TG_ARGV[0]
        (...)

3) use some 'magic' function which fetches the primary
   key of the row referred to by 'OLD' or 'NEW'...

Alas I haven't found any documentation or anything in
the various archives on how to do this, and random
"guess-a-syntax" attempts have also proved remarkably unsuccessful.

Is what I am trying to do possible, and if so how; or am
I barking up the wrong line of enquiry entirely?


Any advice gratefully accepted

yrs

Grant Table
grant.table@easypublish.de

"Table Design by Name and By Nature"