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.
Near the top of this page: 42.7. Cursors
That made me wonder where, specifically, this would be more efficient than a plain old function call.
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;The customers table I'm working with has 20,000 rows so with the cross joins that should be 8e+12 rows (which would take a while to fully evaluate!). The select statement at the end appears to confirm that the function is reading all rows (I had to cancel it after several seconds -- way more than to just return the first row)
That leads me to ask:
If (and under what circumstances) PostgreSQL evaluates functions lazily (returning rows as requested by the caller) or eagerly (evaluation all rows before returning the first one)?
