Search Postgresql Archives

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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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



[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux