Re: select unnest(), unnest()

Thank you, Tom. Lateral works.

happy easter!

2018-04-01 21:49 GMT+02:00 Tom Lane <[email protected]>:

> Jasmin Dizdarevic <[email protected]> writes:
> > select id, unnest(string_to_array(advisor,',')), unnest
> > (string_to_array(branch,','))
> > from configuration;
>
> Yes, the behavior for cases like this changed in PG 10.  Read the
> release notes:
>
>     * Change the implementation of set-returning functions appearing in a
>     query's SELECT list (Andres Freund)
>
>     Set-returning functions are now evaluated before evaluation of scalar
>     expressions in the SELECT list, much as though they had been placed in
>     a LATERAL FROM-clause item. This allows saner semantics for cases
>     where multiple set-returning functions are present. If they return
>     different numbers of rows, the shorter results are extended to match
>     the longest result by adding nulls. Previously the results were cycled
>     until they all terminated at the same time, producing a number of rows
>     equal to the least common multiple of the functions' periods. In
>     addition, set-returning functions are now disallowed within CASE and
>     COALESCE constructs. For more information see Section 37.4.8.
>
> > - Is there a way to change the behaviour of pgsql to produce output like
> > 9.6?
> > - A smooth sql-workaround?
>
> LATERAL would probably help you; see the examples in 37.4.8.
>
> https://www.postgresql.org/docs/10/static/xfunc-sql.html#
> XFUNC-SQL-FUNCTIONS-RETURNING-SET
>
>                         regards, tom lane
>