agora inbox for pgsql-sql@postgresql.org  

help / color / mirror / Atom feed

From: Tom Lane <tgl@sss.pgh.pa.us>
To: Jasmin Dizdarevic <jasmin.dizdarevic@gmail.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: select unnest(), unnest()
Date: Sun, 01 Apr 2018 15:49:21 -0400
Message-ID: <11707.1522612161@sss.pgh.pa.us> (raw)
In-Reply-To: <CAOveQuP6xqLzopbp+pMp8sbbSvKwHj2z6BXM_jEq9onqXwo+uQ@mail.gmail.com>
References: <CAOveQuP6xqLzopbp+pMp8sbbSvKwHj2z6BXM_jEq9onqXwo+uQ@mail.gmail.com>

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

view thread (3+ messages)  latest in thread

Message-ID: <11707.1522612161@sss.pgh.pa.us>
Permalink:  ../11707.1522612161@sss.pgh.pa.us/
Also on:    postgresql.org/message-id/11707.1522612161@sss.pgh.pa.us

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: tgl@sss.pgh.pa.us, jasmin.dizdarevic@gmail.com
  Subject: Re: select unnest(), unnest()
  In-Reply-To: <11707.1522612161@sss.pgh.pa.us>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox