Received: from post.mail.demon.net (post-20.mail.demon.net [194.217.242.27]) by hub.org (8.9.2/8.9.1) with ESMTP id PAA53096 for ; Mon, 15 Feb 1999 15:34:42 -0500 (EST) (envelope-from Justin_Subert@subitek.demon.co.uk) Received: from [193.237.161.195] (helo=subitek.demon.co.uk) by post.mail.demon.net with esmtp (Exim 2.10 #2) id 10CUj2-0000D1-00 for pgsql-sql@postgresql.org; Mon, 15 Feb 1999 20:34:40 +0000 Received: from subitek.demon.co.uk (justin.subitek.demon.co.uk [192.168.0.1]) by subitek.demon.co.uk (8.8.7/8.8.7) with ESMTP id UAA10700 for ; Mon, 15 Feb 1999 20:34:40 GMT Message-ID: <36C884FD.38C3C5ED@subitek.demon.co.uk> Date: Mon, 15 Feb 1999 20:35:09 +0000 From: Justin Subert X-Mailer: Mozilla 4.5 [en] (Win98; I) X-Accept-Language: en MIME-Version: 1.0 To: pgsql-sql Subject: [SQL] difficulties combining nextval and rules Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit 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.