Skip site navigation (1) Skip section navigation (2)

Re: Bug #485: strange behavior when creting rules with serial id

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: ttomov(at)abs(dot)bg, pgsql-bugs(at)postgresql(dot)org
Subject: Re: Bug #485: strange behavior when creting rules with serial id
Date: 2001-10-17 14:40:16
Message-ID: 26815.1003329616@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-bugs
pgsql-bugs(at)postgresql(dot)org writes:
> CREATE RULE insert_test2 AS ON INSERT TO test1 DO INSERT INTO test2
> (sum1, name2, id, id1) VALUES (new.sum, new.name, new.id,
> nextval('"test2_id1_seq"'::text));

"NEW" is a macro, not a variable.  This rule will be expanded to
something like

INSERT INTO test2
(sum1, name2, id, id1) VALUES ('1', '2', NEXTVAL('"test1_id_seq"'::text),
nextval('"test2_id1_seq"'::text));

which means that nextval('test1_id_seq') is evaluated twice, once during
the rule and once during the actual insert into test1.

You can't do what you want with a rule; you'll need to use a
trigger instead.

			regards, tom lane

In response to

pgsql-bugs by date

Next:From: Tom LaneDate: 2001-10-17 16:38:43
Subject: Re: Bug #484: TIMESTAMP arithmetic insconsistencies
Previous:From: Stephan SzaboDate: 2001-10-17 12:12:54
Subject: Re: Bug #485: strange behavior when creting rules with

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group