Re: rule causes nextval() to be invoked twice

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?
>
>
>