>>>>> "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)
