Thank you all for the detailed explanations. I think the most disappointing is this bit:
2. Table function called in the FROM clause
Table functions in the FROM clause, e.g. SELECT ... FROM myfunc();
are always evaluated eagerly.
Which more or less matches my toy example. OTOH Tom mentioned that marking the function STABLE effectively makes it lazy since it is inlined and my testing confirms that for SQL language functions. Alas not for PL/pgSQL functions, though.
Back to where I started in my top post: I became interested in this due to the doc note on returning a cursor and that it can be an efficient way to handle large result sets. I suppose that implies lazy evaluation. Does that mean that if I need plpgsql for a function for he language's power yet want the results to be returned lazily, a cursor is the (only?) way to go?
On Fri, Jan 3, 2020 at 10:07 PM Andrew Gierth <andrew@xxxxxxxxxxxxxxxxxxxx> wrote:
>>>>> "Gerald" == Gerald Britton <gerald.britton@xxxxxxxxx> writes:
Gerald> That leads me to ask:
Gerald> If (and under what circumstances) PostgreSQL evaluates
Gerald> functions lazily (returning rows as requested by the caller) or
Gerald> eagerly (evaluation all rows before returning the first one)?
This isn't trivial to answer because it depends significantly on the
language the function is written in and how it was called. The first
matching rule below controls what happens.
1. LANGUAGE SQL with inlining
Table functions in language SQL are candidates for inlining, see
https://wiki.postgresql.org/wiki/Inlining_of_SQL_functions
If an SQL-language function is inlined, then it behaves exactly as
though the function body was written inline, which means it is evaluated
as lazily as the query plan otherwise permits (for example, if there's a
non-indexable ORDER BY clause, then clearly all the values have to be
fetched before any are returned).
2. Table function called in the FROM clause
Table functions in the FROM clause, e.g. SELECT ... FROM myfunc();
are always evaluated eagerly.
3. LANGUAGE SQL without inlining, in the select-list
If the final statement of an SQL function is a plain select with no
wCTEs, then it is evaluated lazily: the first fetch will execute
everything up to the first row of the final select, and subsequently one
row will be fetched at a time. If the final statement is a DML statement
with a RETURNING clause, or contains wCTEs, then it is evaluated
eagerly.
4. LANGUAGE C / INTERNAL
C-language functions (and therefore internal functions too) can choose
whether to use value-per-call mode or materialize mode. Materialize mode
is always "eager", but value-per-call mode is sometimes still eager (as
in case 2 above); it can only be lazy if no preceding rule forced it to
be otherwise.
Most built-in table functions use value-per-call mode (a notable
exception being the functions in the tablefunc module).
5. LANGUAGE PLPGSQL, PLPERL, PLTCL
Plpgsql, plperl, and pltcl functions are always evaluated eagerly.
6. LANGUAGE plpython
Plpython functions that return an iterator run in value-per-call mode,
with a "next" call on the iterator for each row. To what extent this is
a lazy or eager evaluation depends on the python code.
7. Other PL languages
For non-core PL languages the documentation or source code may indicate
whether the language uses materialize mode or value-per-call mode. (Most
languages are probably not well equipped to do value-per-call mode. One
that does allow it is pl/lua, which runs table functions as coroutines.)
--
Andrew (irc:RhodiumToad)
Gerald Britton, MCSE-DP, MVP
LinkedIn Profile: http://ca.linkedin.com/in/geraldbritton
LinkedIn Profile: http://ca.linkedin.com/in/geraldbritton
