INSERT RULE doesn't allow OLD, so how does one work with serial datatypes?
От | Karen Hill |
---|---|
Тема | INSERT RULE doesn't allow OLD, so how does one work with serial datatypes? |
Дата | |
Msg-id | 1147554674.012216.299000@y43g2000cwc.googlegroups.com обсуждение исходный текст |
Ответы |
Re: INSERT RULE doesn't allow OLD, so how does one work with serial datatypes?
|
Список | 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);
В списке pgsql-general по дате отправления: