Re: select unnest(), unnest()

Thank you, Tom. Lateral works.

happy easter!

2018-04-01 21:49 GMT+02:00 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>:

> Jasmin Dizdarevic <jasmin(dot)dizdarevic(at)gmail(dot)com> 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
>