Message-ID: <4706DBB8.8010000@Sheeky.Biz>
Date: Sat, 06 Oct 2007 10:20:00 +0930
From: Shane Ambler <pgsql@Sheeky.Biz>
User-Agent: Thunderbird 2.0.0.6 (Macintosh/20070728)
MIME-Version: 1.0
To: Tom Lane <tgl@sss.pgh.pa.us>
CC: PostgreSQL Hackers <pgsql-hackers@postgresql.org>
Subject: Re: [SQL] Why does the sequence skip a number with generate_series?
References: 
 <Pine.LNX.4.64.0710021716490.4701@discord.home.frostconsultingllc.com>
	<20071003175808.P16566@megazone.bigpanda.com>
	<4704F7CA.4070200@Sheeky.Biz>
	<20071004144656.GK6176@alvh.no-ip.org>
	<21286.1191511096@sss.pgh.pa.us>
In-Reply-To: <21286.1191511096@sss.pgh.pa.us>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Tom Lane wrote:
> Alvaro Herrera <alvherre@commandprompt.com> writes:
>> Shane Ambler wrote:
>>>> CREATE TABLE jefftest ( id serial, num int );
>>>> INSERT INTO jefftest (num) values (generate_series(1,10));
>>>> INSERT INTO jefftest (num) values (generate_series(11,20));
>>>> INSERT INTO jefftest (num) values (generate_series(21,30));
> 
>> Don't use set-returning functions in "scalar context".
> 
> I think what is actually happening is that the expanded targetlist is
> 
> 	nextval('seq'), generate_series(1,10)
> 
> On the eleventh iteration, generate_series() returns ExprEndResult to
> show that it's done ... but the 11th nextval() call already happened.
> If you switched the columns around, you wouldn't get the extra call.
> 
> If you think that's bad, the behavior with multiple set-returning
> functions in the same targetlist is even stranger.  The whole thing
> is a mess and certainly not something we would've invented if we
> hadn't inherited it from Berkeley.
> 
> 			regards, tom lane

Would a re-write be something worth adding to the todo list?

and/or maybe add something about this to the manual?


-- 

Shane Ambler
pgSQL@Sheeky.Biz

Get Sheeky @ http://Sheeky.Biz