[SQL] difficulties combining nextval and rules
От | Justin Subert |
---|---|
Тема | [SQL] difficulties combining nextval and rules |
Дата | |
Msg-id | 36C884FD.38C3C5ED@subitek.demon.co.uk обсуждение исходный текст |
Список | 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.
В списке pgsql-sql по дате отправления: