Re: Behavior of nextval() and currval()

From: Mike Mascari <mascarm(at)mascari(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: pgsql-general(at)postgresql(dot)org, Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us>
Subject: Re: Behavior of nextval() and currval()
Date: 2001-11-13 19:11:22
Message-ID: 3BF1705A.2336FF5@mascari.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Command Prompt, Inc. 2001-11-13 20:28:50 XML/PostgreSQL Application server now available
Previous Message Tom Lane 2001-11-13 18:53:33 Re: Behavior of nextval() and currval()