Gerald Britton <gerald.britton@gmail.com> writes:
> I'm learning to write functions in PostgreSQL. When I got to the
> documentation chapter on cursors, I came across this interesting comment:
> A more interesting usage is to return a reference to a cursor that a
> function has created, allowing the caller to read the rows. This provides
> an efficient way to return large row sets from functions.
This is more or less unrelated to your question, or at least to your
example, because you didn't use a cursor.
> I made up a little test function and call:
> CREATE FUNCTION foo() RETURNS SETOF customers
> LANGUAGE SQL AS $$
> SELECT c.* FROM customers c
> CROSS JOIN customers x
> CROSS JOIN customers y;$$;
> SELECT * FROM foo() LIMIT 1;
At the moment, set-returning functions in FROM are generally evaluated
"eagerly", ie run to completion before the calling query does anything
else. There's been some work recently towards improving that, but it's
not done yet.
This particular example could be improved, because SQL-language functions
can potentially be inlined. Yours wasn't because it's volatile by
default, and for somewhat arcane semantic reasons we won't inline
volatiles. But if you were to mark that function stable it'd get
inlined, and then it'd respond promptly to the LIMIT 1.
Another point is that the rules are different for set-returning functions
in a targetlist. For example,
select foo() limit 1;
is fast with your example despite the lack of inlining. That's because
that case actually is done row-by-row, with the function yielding control
after each row. So we have the infrastructure for that, it just hasn't
been exploited in the function-in-FROM case.
And, if you weren't confused yet: all of this depends on the particular
PL that you're considering. Of the PLs included with core PG, I think
only SQL-language functions can do the above --- the other ones are
strictly execute-to-completion. I don't know offhand about third-party
PLs.
regards, tom lane