[BUGS] BUG #1705: nextval being evaluated more than once

[Nick Farrell]

The following bug has been logged online:

Bug reference:      1705
Logged by:          Nick Farrell
Email address:      [EMAIL PROTECTED]
PostgreSQL version: 8.0.3
Operating system:   ubuntu linux (warty warthog)
Description:        nextval being evaluated more than once
Details: 

If I use embed nextval in a string which is EVALUATEd, the insert rule below
does not get the correct primary key value, but the next one! ie: NEW.p1 is
not the actual value of p1, but is the result of re-executing nextval().

Workaround is to evaluate nextval in the function, and EVALUATE with a
literal key value. This is shown in the good_fn below.

Apologies in advance if you already know about this one, or this is someone
intended behaviour.

Nick.

--------------- snip ---------------------
create table a (
    p1 integer primary key,
    v1 integer
);
create sequence s;

create table b (
    p2 serial,
    fk integer not null references a
);

create rule a_ins AS ON INSERT TO a DO INSERT INTO b (fk) values (NEW.p1);

create or replace function bad_fn() returns integer AS '
DECLARE
    result INTEGER;
BEGIN
    EXECUTE '' insert into a values (nextval(''''s''''), 2); '';
    result := 0;
    return result;
END;
' language plpgsql;

create or replace function good_fn() returns integer AS '
DECLARE
    result INTEGER;
BEGIN
    result := 0;
    EXECUTE '' insert into a values ('' || nextval(''s'') || '', 2); '';
    return result;
END;
' language plpgsql;

select good_fn();
select good_fn();
select bad_fn();

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
      subscribe-nomail command to [EMAIL PROTECTED] so that your
      message can get through to the mailing list cleanly