Hi all,

I send a similar problem to the SQL list but had no replies. Perhaps you
can help.

I have noticed a problem/bug/feature using nextval and rules. What I
would expect from the example below is for the nextval of the sequence
to be evaluated once and this value to be used. What appears to be
happening is the nextval function is being referenced and then being
re-evaluated a number of times.

Here is the example of the problem:

CREATE SEQUENCE seq1;
CREATE

CREATE TABLE tab1 (
        a INTEGER
);
CREATE

CREATE TABLE tab2 (
        b1 INTEGER,
        b2 INTEGER,
        b3 INTEGER,
        b4 INTEGER
);
CREATE

CREATE RULE rule1 AS ON INSERT TO tab1 DO INSERT INTO tab2 VALUES
(NEW.a, NEW.a, NEW.a,
NEW.a);
CREATE

INSERT INTO tab1 VALUES (NEXTVAL('seq1'));
INSERT 29288 1

SELECT * FROM tab1;
a
-
5
(1 row)


SELECT * FROM tab2;
b1|b2|b3|b4
--+--+--+--
 1| 2| 3| 4
(1 row)


----

The only way I can see of getting round this is to use triggers and c
code.

Does anybody have any ideas on this?

Thanks,
Justin.


Sun, 07 Mar 1999 23:51:40 +0000
Justin Subert <Justin_Subert@subitek.demon.co.uk>
[HACKERS] Problem with nextval and rules