PostgreSQL's™ stored function can return results by means of a refcursor value. A refcursor.
As an extension to JDBC, the PostgreSQL™ JDBC driver can return refcursor values as ResultSet values.
Example 6.2. Getting refcursor values from a function
When calling a function that returns a refcursor you must cast the return type of getObject to a ResultSet
// Turn transactions off.
con.setAutoCommit(false);
// Procedure call.
CallableStatement proc = con.prepareCall("{ ? = call doquery ( ? ) }");
proc.registerOutParameter(1, Types.Other);
proc.setInt(2, -1);
proc.execute();
ResultSet results = (ResultSet) proc.getObject(1);
while (results.next()) {
// do something with the results...
}
results.close();
proc.close();
It is also possible to treat the refcursor return value as a distinct type in itself. The JDBC driver provides the org.postgresql.PGRefCursorResultSet class for this purpose.
Example 6.3. Treating refcursor as a distinct type
con.setAutoCommit(false);
CallableStatement proc = con.prepareCall("{ ? = call doquery ( ? ) }");
proc.registerOutParameter(1, Types.Other);
proc.setInt(2, 0);
org.postgresql.PGRefCursorResultSet refcurs
= (PGRefCursorResultSet) con.getObject(1);
String cursorName = refcurs.getRefCursor();
proc.close();