[SQL] difficulties combining nextval and rules

From: Justin Subert <Justin_Subert(at)subitek(dot)demon(dot)co(dot)uk>
To: pgsql-sql <pgsql-sql(at)postgresql(dot)org>
Subject: [SQL] difficulties combining nextval and rules
Date: 1999-02-15 20:35:09
Message-ID: 36C884FD.38C3C5ED@subitek.demon.co.uk
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

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.

Browse pgsql-sql by date

  From Date Subject
Next Message jose' soares 1999-02-16 08:51:47 Re: [SQL] Booleans and Casting
Previous Message Remigiusz Sokolowski 1999-02-15 13:50:00 Re: [SQL] index on aggregate function