Marcin Mazurek <M.Mazurek@poznan.multinet.pl> writes:
> Simple example to make things clearer.
> CREATE TABLE tab (id INT SERIAL PRIMARY KEY, sth TEXT); --main table
> CREATE TABLE log_tab(id INT, sth TEXT); --table to maintain logs in it
> CREATE RULE tab_log_ins AS ON INSERT TO tab DO
> INSERT INTO log_tab VALUES (new.id, new.sth);
> INSERT INTO tab (sth) VALUES ('something');
> when I insert new raw in tab, id field differs (rises by one) from id in
> log_tab, how can i avoid it?
At least at the moment, the only way is to use a trigger.
The problem is this. Your insert is transformed by the parser to include
the defaults for the missing columns:
INSERT INTO tab (id, sth) VALUES (nextval('id_seq'), 'something');
Then the rule is applied. That's also fundamentally a textual
transformation, so what actually gets executed is equivalent to
INSERT INTO log_tab VALUES (nextval('id_seq'), 'something');
INSERT INTO tab (id, sth) VALUES (nextval('id_seq'), 'something');
See the problem? What you want is to lay your hands on the actual
values that are getting inserted into "tab", and a rule cannot do that.
But a trigger does exactly that.
I am not sure whether this behavior of rules is a bug or a feature.
I am sure that it would be difficult to change...
regards, tom lane
