From: Justin Subert <Justin_Subert@subitek.demon.co.uk>
To: pgsql-sql <pgsql-sql@postgresql.org>
Subject: [SQL] difficulties combining nextval and rules
Date: Mon, 15 Feb 1999 20:35:09 +0000
Message-ID: <36C884FD.38C3C5ED@subitek.demon.co.uk> (raw)
All,
I am having difficulties combining nextval and rules. Here is an
example:
CREATE SEQUENCE seq1;
CREATE TABLE b (
b1 INTEGER,
b2 TEXT
);
CREATE TABLE a (
a1 INTEGER DEFAULT NEXTVAL('seq1'),
a2 TEXT
);
CREATE RULE rule1 AS ON INSERT TO a DO INSERT INTO b VALUES (NEW.a1,
NEW.a2);
INSERT INTO a (a2) VALUES ('Test 1a');
INSERT INTO a (a2) VALUES ('Test 1b');
INSERT INTO a VALUES (NEXTVAL('seq1'), 'Test 2a');
INSERT INTO a VALUES (NEXTVAL('seq1'), 'Test 2b');
INSERT INTO a VALUES (20, 'Test 3a');
INSERT INTO a VALUES (30, 'Test 3b');
SELECT * FROM a;
a1|a2
--+-------
2|Test 1a
4|Test 1b
6|Test 2a
8|Test 2b
20|Test 3a
30|Test 3b
(6 rows)
SELECT * FROM b;
b1|b2
--+-------
1|Test 1a
3|Test 1b
5|Test 2a
7|Test 2b
20|Test 3a
30|Test 3b
(6 rows)
----
It would appear that rather than retrieving the next value from the
sequence and then using that value, it references the function nextval
and obtains a new sequence value at each reference. I am unsure if this
is a feature or a bug, but in any case I have been unsuccessful in
getting the result I require.
What I would like is for the sequence value to be obtained once and this
value to be put into a1 and b1. Does anyone have any ideas?
TIA,
Justin.
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 from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-sql@postgresql.org
Cc: Justin_Subert@subitek.demon.co.uk
Subject: Re: [SQL] difficulties combining nextval and rules
In-Reply-To: <36C884FD.38C3C5ED@subitek.demon.co.uk>
References: <36C884FD.38C3C5ED@subitek.demon.co.uk>
* 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