Re: select unnest(), unnest() - Mailing list pgsql-sql

Jasmin Dizdarevic <jasmin.dizdarevic@gmail.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