Обсуждение: [BUGS] bug in RULE insert
<br /><br />Strange the rule works for an insertion<br /><br /><br />example:<br /><br /><p>CREATE TABLE public.test<br />(<br/>id bigserial NOT NULL,<br />name text<br />);<p><br />ALTER TABLE public.test<br />ADD CONSTRAINT test_constraint_pkeyPRIMARY KEY(id);<p><br />CREATE TABLE public.v_test<br />(<br />id bigserial NOT NULL,<br />v_id bigint,<br/>v_name text<br />);<p><br />ALTER TABLE public.v_test<br />ADD CONSTRAINT v_test_constraint_pkey PRIMARY KEY(id);<p><br/>CREATE OR REPLACE RULE insert AS<br />ON INSERT TO test DO INSERT INTO v_test (v_id, v_name)<br />VALUES(new.id, new.name);<p><br /><br /><br />then execute<br /><br /><p>insert into test(name)<br />values <br />('1'),<br/>('2'),<br />('3')<br /><br />values in test.id <> v_test.v_id<p>Tested on postgresql 9.5/9.6 install fromPPA <span data-mce-style="font-family: monospace; font-size: 10.764px; background-color: #f7f7f7;" style="font-family:monospace; font-size: 10.764px; background-color: #f7f7f7;">http://apt.postgresql.org/pub/repos/apt</span><br/>OS ubuntu-sever 14.04/16.04<p>Bbest regards<br />Alexander Pokolenko.<br/>
Александр <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