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?  (Martijn van Oosterhout <kleptog@svana.org>)
Список 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 по дате отправления:

Предыдущее
От: Russ Brown
Дата:
Сообщение: Re: GUI Interface
Следующее
От: Martijn van Oosterhout
Дата:
Сообщение: Re: INSERT RULE doesn't allow OLD, so how does one work with serial datatypes?