Обсуждение: Set returning functions in the SELECT list

I have a question about the behavior of SRFs in the SELECT list.
Consider the following example:

select
    generate_series(1,2),
    generate_series(1,2)
;

And its output:

 generate_series | generate_series
-----------------+-----------------
               1 |               1
               2 |               2

And then this example:

select
    generate_series(1,2),
    generate_series(1,3)
;

 generate_series | generate_series
-----------------+-----------------
               1 |               1
               2 |               2
               1 |               3
               2 |               1
               1 |               2
               2 |               3

My question is "why?" I actually expected the second behavior in both
cases. Also, I'm not sure where this difference in behavior is
documented. I looked at:
http://www.postgresql.org/docs/current/static/xfunc-sql.html#XFUNC-SQL-FUNCTIONS-RETURNING-SET
but it doesn't seem to be explicitly mentioned there.

And yes, I understand doing this is deprecated and my results would
probably be better achieved with LATERAL when 9.3 comes out.

-Ryan Kelly

Ryan Kelly <rpkelly22@gmail.com> writes:
> I have a question about the behavior of SRFs in the SELECT list.

If you have more than one in a select list, the number of resulting rows
is the least common multiple of their periods, because the select list
gets cycled until they all terminate at the same time.

> Also, I'm not sure where this difference in behavior is
> documented.

AFAIK it's not documented.  Every time it comes up there are arguments
about whether to change it, and nobody has wanted to make a permanent
commitment to this behavior by documenting it.  (On the other hand,
since it's been like this since Berkeley days, it would be pretty hard
to make an adequate case for changing it and likely breaking
applications ...)

> And yes, I understand doing this is deprecated and my results would
> probably be better achieved with LATERAL when 9.3 comes out.

Yeah, LATERAL has a lot less semantic messiness to it.

            regards, tom lane

On 2012-11-16, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Ryan Kelly <rpkelly22@gmail.com> writes:
>> I have a question about the behavior of SRFs in the SELECT list.
>
> If you have more than one in a select list, the number of resulting rows
> is the least common multiple of their periods, because the select list
> gets cycled until they all terminate at the same time.
>
>> Also, I'm not sure where this difference in behavior is
>> documented.
>
> AFAIK it's not documented.  Every time it comes up there are arguments
> about whether to change it, and nobody has wanted to make a permanent
> commitment to this behavior by documenting it.  (On the other hand,
> since it's been like this since Berkeley days, it would be pretty hard
> to make an adequate case for changing it and likely breaking
> applications ...)
>
>> And yes, I understand doing this is deprecated and my results would
>> probably be better achieved with LATERAL when 9.3 comes out.
>
> Yeah, LATERAL has a lot less semantic messiness to it.

for now you can wrap generate_series in as many CTEs as you want and
do a join if you need a cross-product.

--
⚂⚃ 100% natural