Re: Behavior of nextval() and currval()

Tom Lane wrote:
>
> Mike Mascari <mascarm@mascari.com> writes:
> > I've switched to using a CREATE TEMPORARY TABLE AS SELECT..,
> > INSERT..SELECT to avoid the scenario.
>
> Uh, you mean something like
>
>         select a, a from (select nextval('foo') as a) as b;

I'm not that clever. I just did:

CREATE TEMPORARY TABLE foo AS
SELECT nextval('foo') as a, 0 as b, ...
FROM source;

INSERT INTO bar
SELECT a, a
FROM foo;

instead of:

INSERT INTO bar
SELECT nextval('foo'), currval('foo'), ...
FROM source;

>
> That might surprise you even more :-(
>

You mean:

test=# create table test (key int4 not null);
CREATE
test=# insert into test values (1);
INSERT 803954 1
test=# insert into test values (2);
INSERT 803955 1
test=# select a, a from (select nextval('foo') as a) as b, test;
 a | a
---+---
 4 | 4
 4 | 4 <--- That should be 5?

>
> Perhaps the planner shouldn't pull up subqueries whose targetlists
> include any noncachable functions.  This needs more thought.
>
>                         regards, tom lane

Mike Mascari
mascarm@mascari.com