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

Tom Lane wrote:
> Alvaro Herrera <alvherre(at)commandprompt(dot)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(at)Sheeky(dot)Biz

Get Sheeky @ http://Sheeky.Biz