Re: Are PostgreSQL functions that return sets or tables evaluatedlazily or eagerly?

>>>>> "Gerald" == Gerald Britton <gerald.britton@gmail.com> 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)