Cross post from Stack Overflow: https://stackoverflow.com/questions/59554144/are-postgresql-functions-that-return-sets-or-tables-evaluated-lazily-or-eagerly
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 <https://www.postgresql.org/docs/current/plpgsql-cursors.html> 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)? -- Gerald Britton, MCSE-DP, MVP LinkedIn Profile: http://ca.linkedin.com/in/geraldbritton
