[SQL] difficulties combining nextval and rules - Mailing list 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.