From: Dmitry Tkach <dmitry@openratings.com>
To: paul cannon <pik@debian.org>
Cc: pgsql-sql@postgresql.org
Subject: Re: rule causes nextval() to be invoked twice
Date: Wed, 23 Jul 2003 10:30:58 -0400
Message-ID: <3F1E9C22.3020905@openratings.com> (raw)
In-Reply-To: <20030723020853.GG24912@fslc.usu.edu>
References: <20030723014700.GE24912@fslc.usu.edu>
<20030723020853.GG24912@fslc.usu.edu>
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?
>
>
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: dmitry@openratings.com, pik@debian.org
Subject: Re: rule causes nextval() to be invoked twice
In-Reply-To: <3F1E9C22.3020905@openratings.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox