Re: Behavior of nextval() and currval()

Mike Mascari <mascarm(at)mascari(dot)com> writes:
> Tom Lane wrote:
>> 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?

That works okay, but there are variant cases where the planner
will collapse the inner and outer selects, replacing each reference
in the outer select with a copy of the expression from the inner
select. For example,

regression=# select key, a, a from (select key, nextval('foo') as a from test) ss;
key | a | a
-----+---+---
1 | 2 | 3
2 | 4 | 5
(2 rows)

regression=#

This is probably not good when the inner expression contains noncachable
functions ... but I'm hesitant to disable the potential optimization
from collapsing the selects in "harmless" cases such as use of
timestamp-dependent functions. We really need a finer-grain notion
of function cachability...

regards, tom lane