From: | Grant Table <grant(dot)table(at)easypublish(dot)de> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Triggers, functions and column names: a poser |
Date: | 2001-11-17 00:37:05 |
Message-ID: | 200111170135.CAA26861@post.webmailer.de |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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(dot)table(at)easypublish(dot)de
"Table Design by Name and By Nature"
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2001-11-17 00:53:43 | TOAST performance (was Re: [GENERAL] Delete Performance) |
Previous Message | Shane Wegner | 2001-11-16 23:36:12 | Unsigned types |