Re: Set returning functions in select column list

On Feb 16, 2012, at 14:19, Jack Christensen <jackc@hylesanderson.edu> wrote:

> Can someone explain how set returning functions in a select clause work?

You apparently just did...it CROSS JOINs except in the special case where multiple functions each return the same
numberof records (generally due to them all operating on the same sized input - like unnest(array) - in which case it
synchronizesthe generated rows. 

>
> It seems that it is doing some sort of implicit cross join.
>
> deliverance_development=# select id, generate_series(1, 3) from users;
> id | generate_series
> ----+-----------------
>  0 |               1
>  0 |               2
>  0 |               3
>  1 |               1
>  1 |               2
>  1 |               3
> (6 rows)
>
> But if multiple set returning functions that return the same number of rows are in the same select it doesn't further
crossjoin it. 
>
> deliverance_development=# select id, generate_series(1, 3), generate_series(4, 6) from users;
> id | generate_series | generate_series
> ----+-----------------+-----------------
>  0 |               1 |               4
>  0 |               2 |               5
>  0 |               3 |               6
>  1 |               1 |               4
>  1 |               2 |               5
>  1 |               3 |               6
> (6 rows)
>
>
> But if the set returning functions return a different number of rows then it goes back to a cross join.
>
> deliverance_development=# select id, generate_series(1, 3), generate_series(4, 5) from users;
> id | generate_series | generate_series
> ----+-----------------+-----------------
>  0 |               1 |               4
>  0 |               2 |               5
>  0 |               3 |               4
>  0 |               1 |               5
>  0 |               2 |               4
>  0 |               3 |               5
>  1 |               1 |               4
>  1 |               2 |               5
>  1 |               3 |               4
>  1 |               1 |               5
>  1 |               2 |               4
>  1 |               3 |               5
> (12 rows)
>
>
> I really don't understand what is going on here. I have checked Google and the PostgreSQL docs, but it appears either
Ido not know the key words to search for or it is sparsely documented. 
>
> --
> Jack Christensen
> jackc@hylesanderson.edu
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general