INSERT RULE doesn't allow OLD, so how does one work with serial datatypes?

From: "Karen Hill" <karen_hill22(at)yahoo(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: INSERT RULE doesn't allow OLD, so how does one work with serial datatypes?
Date: 2006-05-13 21:11:14
Message-ID: 1147554674.012216.299000@y43g2000cwc.googlegroups.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I'm having a bit of mystery in solving a postgresql puzzle. I have a
table that when it gets inserted or updated or deleted it is logged
into a log table. The log table contains who (current_user) did the
insert/update/delete the CURRENT_TIMESTAMP. Everything works great
except the INSERT because I cannot use OLD and NEW increments the
serial twice!

CREATE TABLE ttest (bpchar, instime abstime, prikey serial PRIMARY
KEY);
CREATE TABLE ttest_log ( value bpchar, user bpchar, instime abstime,
modtime abstime , logprikey int4);

CREATE RULE ri AS ON INSERT TO ttest DO
INSERT INTO ttest_log (NEW.value , current_user, CURRENT_TIMESTAMP,
'infinity', NEW.logprikey);
--on the above NEW.logprikey creates two different primary keys!! One
pk for the ttest and pk +1 for ttest_log!

CREATE RULE rupd AS ON UPDATE TO ttest DO
INSERT INTO ttest_log (old.value, current_user, old.instime,
CURRENT_TIMESTAMP);

CREATE RULE rdel AS ON DELETE TO ttest DO
INSERT INTO ttest_log (old.value, current_user, old.instime,
CURRENT_TIMESTAMP);

Responses

Browse pgsql-general by date

  From Date Subject
Next Message syohonn@gmail.com 2006-05-13 21:15:52 Pass in variable from user???
Previous Message Russ Brown 2006-05-13 17:07:31 Re: GUI Interface