Message-ID: <3F1E9C22.3020905@openratings.com>
Date: Wed, 23 Jul 2003 10:30:58 -0400
From: Dmitry Tkach <dmitry@openratings.com>
User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.2.1) Gecko/20021130
MIME-Version: 1.0
To: paul cannon <pik@debian.org>
Cc: pgsql-sql@postgresql.org
Subject: Re: rule causes nextval() to be invoked twice
References: <20030723014700.GE24912@fslc.usu.edu>
	<20030723020853.GG24912@fslc.usu.edu>
In-Reply-To: <20030723020853.GG24912@fslc.usu.edu>
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit

I think, your example would work if you replaced the new.id in the rule 
with curval ('main_id_seq');
... but see Tom's earlier reply - this is still not a very good thing to 
do... For example, it won't work if you try to insert into main anything 
with explicitly specified id (not generated by the sequence), or if you 
insert multiple rows with the single statement (like insert... select), 
or if you do COPY (besides the fact that it doesn't touch sequence, it 
also doesn't invoke rules at all).

To do what you are trying to do, an after trigger seems to be the only 
thing that will work completely.

Dima

paul cannon wrote:

>On Tue, Jul 22, 2003 at 07:47:00PM -0600, paul cannon wrote:
>  
>
>>Until then, I'll have to make a function to do nextval('main_id_seq')
>>with every insert, and have the primary key be INTEGER.
>>    
>>
>
>Nevermind- that doesn't work either! Here's the new sample code:
>
>-- Begin demo SQL
>
>CREATE SEQUENCE main_id_seq;
>CREATE TABLE main (
>        id INTEGER PRIMARY KEY,
>        contents VARCHAR
>);
>
>CREATE TABLE othertable (
>        main_id INTEGER REFERENCES main(id)
>);              
>
>CREATE RULE main_insert AS 
>  ON INSERT TO main DO
>        INSERT INTO othertable VALUES (new.id);
>
>INSERT INTO main(id, contents) VALUES (nextval('main_id_seq'), 'Fails here');
>
>-- End demo SQL
>
>The same thing happens. The rule tries to put 2 into othertable. Surely
>this is a bug?
>
>  
>