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
