Re: Set of cursors from database

On Thu, 31 Aug 2006, student23 wrote:

> I'm want to retrieve a set of cursors but with only one call to
> database. I was wondering about how to write such function in postgres
> and get the results in java.
>

There are two approaches to doing this.  You can either return a SETOF
refcursor or you can return multiple refcursors as OUT parameters.

Building on the example from the documentation for how to call this from
java the two following functions should be helpful:

http://jdbc.postgresql.org/documentation/81/callproc.html#callproc-resultset-refcursor

CREATE FUNCTION setofcursors() RETURNS SETOF refcursor AS '
DECLARE
         curs1 refcursor;
         curs2 refcursor;
BEGIN
         OPEN curs1 FOR SELECT 1;
         RETURN NEXT curs1;
         OPEN curs2 FOR SELECT 2;
         RETURN NEXT curs2;
         RETURN;
END;
' LANGUAGE plpgsql;


CREATE OR REPLACE FUNCTION outcursors(curs1 OUT refcursor, curs2 OUT
refcursor) AS '
BEGIN
         OPEN curs1 FOR SELECT 1;
         OPEN curs2 FOR SELECT 2;
         RETURN;
END;
' LANGUAGE plpgsql;

Kris Jurka