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.
