RETURN NEXT in Postgres Function

Question

I'm currently writing a plpgsql function that will select from a table and produce a substantial amount of data.

I was originally going to return an array, but I found a resource that explains the "RETURN NEXT operation - Very handy for callers.

However, I read this blurb which is concerning:

Note: The current implementation of RETURN NEXT and RETURN QUERY stores the entire result set before returning from the function, as discussed above. That means that if a PL/pgSQL function produces a very large result set, performance might be poor:

Is there a Postgres feature that would allow my function to stream the results back to the caller as they're produced? This might be a limitation of Postgres' architecture in general, but I want to make sure I'm not overlooking something!

Answer 1

If the results are not meant to be used in a subquery but by code, you may use a REFCURSOR in a transaction.

Example:

CREATE FUNCTION example_cursor() RETURNS refcursor AS $$
DECLARE
 c refcursor;
BEGIN
 c:='mycursorname';
 OPEN c FOR select * from generate_series(1,100000);
 return c; 
end;
$$ language plpgsql;

Usage for the caller:

BEGIN;
SELECT example_cursor();
 [output: mycursor]
FETCH 10 FROM mycursor;
 Output:
 generate_series 
-----------------
 1
 2
 3
 4
 5
 6
 7
 8
 9
 10
CLOSE mycursor;
END;

When not interested in piecemeal retrieval, FETCH ALL FROM cursorname may also be used to stream all results to the caller in one step.

• Craig Ringer: Is it just me, btw, or is the inability to use FETCH in PL/PgSQL or in a subquery a very frustrating limitation?
• Daniel Vérité: @Craig: it can't be used in subqueries but constructs like FOR vars in FETCH 10 FROM mycursor LOOP or RETURN QUERY FETCH... are allowed in plpgsql.
• Jmoney38: Thanks for the input - I did come across REFCURSOR, but was hoping for something I could use in a sub expression as well. This will suffice for now.