Triggers, functions and column names: a poser

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(dot)table(at)easypublish(dot)de

"Table Design by Name and By Nature"