bug in RULE insert
Strange the rule works for an insertion
example:
CREATE TABLE public.test
(
id bigserial NOT NULL,
name text
);
ALTER TABLE public.test
ADD CONSTRAINT test_constraint_pkey PRIMARY KEY(id);
CREATE TABLE public.v_test
(
id bigserial NOT NULL,
v_id bigint,
v_name text
);
ALTER TABLE public.v_test
ADD CONSTRAINT v_test_constraint_pkey PRIMARY KEY(id);
CREATE OR REPLACE RULE insert AS
ON INSERT TO test DO INSERT INTO v_test (v_id, v_name)
VALUES (new.id, new.name);
then execute
insert into test(name)
values
('1'),
('2'),
('3')
values in test.id <> v_test.v_id
Tested on postgresql 9.5/9.6 install from PPA http://apt.postgresql.org/pub/repos/apt
OS ubuntu-sever 14.04/16.04
Bbest regards
Alexander Pokolenko.
=?UTF-8?B?0JDQu9C10LrRgdCw0L3QtNGA?= <alexander_8901@mail.ru> writes:
Strange the rule works for an insertion
This is expected behavior, because the rule works like a macro, and
you have a volatile argument (that is, the nextval() call for the
serial column's default) being passed to it and thereby being executed
twice. IOW, what you wrote is equivalent to
insert into test(id, name)
values
(nextval('test_id_seq'), '1'),
(nextval('test_id_seq'), '2'),
(nextval('test_id_seq'), '3');
and that executes, then the rule causes this to also be executed:
insert into v_test (v_id, v_name)
values
(nextval('test_id_seq'), '1'),
(nextval('test_id_seq'), '2'),
(nextval('test_id_seq'), '3');
What you seem to want would be much more reliably done with a trigger.
regards, tom lane
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs