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

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(at)tao11(dot)riddles(dot)org(dot)uk>
wrote:

> >>>>> "Gerald" == Gerald Britton <gerald(dot)britton(at)gmail(dot)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)
>

--
Gerald Britton, MCSE-DP, MVP
LinkedIn Profile: http://ca.linkedin.com/in/geraldbritton