Re: WIP patch: convert SQL-language functions to return tuplestores

Hi,

In the python language, functions that lazily return collections are called
generators and use the yield keyword instead of return.
http://www.python.org/doc/2.5.2/tut/node11.html#SECTION00111000000000000000000

Maybe having such a concept in PostgreSQL would allow the user to choose
between current behavior (materializing) and lazy computing, with a new
internal API to get done in the executor maybe.

CREATE FUNCTION mygenerator()
returns setof integer
language PLPGSQL
AS $f$
BEGIN
FOR v_foo IN SELECT foo FROM table LOOP
YIELD my_expensive_function(v_foo);
END LOOP;
RETURN;
END;
$f$;

At the plain SQL level, we could expose this with a new function parameter,
GENERATOR maybe?

CREATE FUNCTION my_generator_example(integer, integer)
returns setof integer
generator
language SQL
$f$
SELECT generate_series($1, $2);
$f$;

Maybe we should prefer to add the GENERATOR (or LAZY or whatever sounds good
for a native English speaker) parameter to PL functions to instead of
providing YIELD, having RETURN doing YIELD in this case.

Le mardi 28 octobre 2008, Tom Lane a écrit :
> I suppose, but short of a fundamental rethink of how PL functions work
> that's not going to happen. There's also the whole issue of when do
> side-effects happen (such as before/after statement triggers).

Would it be possible to forbid "generators" when using in those cases?

> Agreed, but I think the fundamental solution there, for simple-select
> functions, is inlining.

Would it be possible to maintain current behavior with ROWS estimator for
functions, even when inlining, as a way to trick the planner when you can't
feed it good enough stats?

> I think the PL side of the problem is the hard part --- if we knew how
> to solve these issues for plpgsql then SQL functions would surely be
> easy.

What about this python idea of GENERATORS and the YIELD control for lazy
evaluation of functions?
--
dim