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);
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 |