2

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!

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.

  • Is it just me, btw, or is the inability to use FETCH in PL/PgSQL or in a subquery a very frustrating limitation? – Craig Ringer Apr 4 '14 at 1:49
  • 2
    @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. – Daniel Vérité Apr 4 '14 at 10:28
  • 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. – Jmoney38 Apr 4 '14 at 12:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.