Gerald Britton <gerald.britton@xxxxxxxxx> 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
