Re: Set returning functions in select column list

On Feb 16, 2012, at 14:19, Jack Christensen <jackc(at)hylesanderson(dot)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 number of records (generally due to them all operating on the same sized input - like unnest(array) - in which case it synchronizes the 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 cross join 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 I do not know the key words to search for or it is sparsely documented.
>
> --
> Jack Christensen
> jackc(at)hylesanderson(dot)edu
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general