pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Justin Subert <Justin_Subert@subitek.demon.co.uk>
To: pgsql-sql <pgsql-sql@postgresql.org>
Subject: [SQL] difficulties combining nextval and rules
Date: Mon, 15 Feb 1999 20:35:09 +0000
Message-ID: <36C884FD.38C3C5ED@subitek.demon.co.uk> (raw)

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.




Message-ID: <36C884FD.38C3C5ED@subitek.demon.co.uk>
Permalink:  ../36C884FD.38C3C5ED@subitek.demon.co.uk/
Also on:    postgresql.org/message-id/36C884FD.38C3C5ED@subitek.demon.co.uk

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-sql@postgresql.org
  Cc: Justin_Subert@subitek.demon.co.uk
  Subject: Re: [SQL] difficulties combining nextval and rules
  In-Reply-To: <36C884FD.38C3C5ED@subitek.demon.co.uk>
  References: <36C884FD.38C3C5ED@subitek.demon.co.uk>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox