Re: [SQL] Why does the sequence skip a number with generate_series?

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