From: Tom Lane <tgl@sss.pgh.pa.us>
To: Andres Freund <andres@anarazel.de>
Cc: Robert Haas <robertmhaas@gmail.com>
Cc: pgsql-hackers@postgresql.org <pgsql-hackers@postgresql.org>
Subject: Re: Implement targetlist SRFs using ROWS FROM() (was Changed SRF in targetlist handling)
Date: Wed, 18 Jan 2017 18:14:26 -0500
Message-ID: <15818.1484781266@sss.pgh.pa.us> (raw)
In-Reply-To: <1080.1484776606@sss.pgh.pa.us>
References: <32021.1484678618@sss.pgh.pa.us>
<20170117185346.z55d3jnsaf24yr4m@alap3.anarazel.de>
<837.1484680521@sss.pgh.pa.us>
<12780.1484747004@sss.pgh.pa.us>
<20170118191924.25lfp6rd6y3kw5sa@alap3.anarazel.de>
<13690.1484767452@sss.pgh.pa.us>
<20170118193420.znochutmnzusd636@alap3.anarazel.de>
<15814.1484769612@sss.pgh.pa.us>
<460325A6-EF35-4A29-A1A0-EEDC3EDCF750@anarazel.de>
<22709.1484771072@sss.pgh.pa.us>
<20170118214702.54b2mdbxce5piwv5@alap3.anarazel.de>
<1080.1484776606@sss.pgh.pa.us>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-hackers>
I wrote:
> I'll try to write something about the SRF-in-CASE issue too. Seeing
> whether we can document that adequately seems like an important part
> of making the decision about whether we need to block it.
Here's what I came up with:
This behavior also means that set-returning functions will be evaluated
even when it might appear that they should be skipped because of a
conditional-evaluation construct, such as CASE or COALESCE. For example,
consider
SELECT x, CASE WHEN x > 0 THEN generate_series(1, 5) ELSE 0 END FROM tab;
It might seem that this should produce five repetitions of input rows
that have x > 0, and a single repetition of those that do not; but
actually it will produce five repetitions of every input row. This is
because generate_series() is run first, and then the CASE expression is
applied to its result rows. The behavior is thus comparable to
SELECT x, CASE WHEN x > 0 THEN g ELSE 0 END
FROM tab, LATERAL generate_series(1,5) AS g;
It would be exactly the same, except that in this specific example, the
planner could choose to put g on the outside of the nestloop join, since
g has no actual lateral dependency on tab. That would result in a
different output row order. Set-returning functions in the select list
are always evaluated as though they are on the inside of a nestloop join
with the rest of the FROM clause, so that the function(s) are run to
completion before the next row from the FROM clause is considered.
So is this too ugly to live, or shall we put up with it?
regards, tom lane
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
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-hackers@postgresql.org
Cc: tgl@sss.pgh.pa.us, andres@anarazel.de, robertmhaas@gmail.com
Subject: Re: Implement targetlist SRFs using ROWS FROM() (was Changed SRF in targetlist handling)
In-Reply-To: <15818.1484781266@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 DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox