PostgreSQL RETURN NEXT Function – How to Use

plpgsqlpostgresqlset-returning-functions

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!

Best Answer

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.

Related Question