I am running into a behavior with a postgresql function with a SETOF refcursor's returning multiple columns. Not sure if there is a different way to retrieve a SETOF refcursor's with variable columns? Alternatively can I return a primitive value and a refcursor from the same function. I tried specifying this as OUT parameters without any luck. In Oracle you can pass this in functions:
Platform:
postgresql-x64-9.0 (PostgreSQL 9.0.1, compiled by Visual C++ build 1500, 64-bit)
Java1.6
JDBC4 Postgresql Driver, Version 9.0-801
Function:
CREATE OR REPLACE FUNCTION test()
RETURNS SETOF refcursor AS
$BODY$
DECLARE
ref1 refcursor;
ref2 refcursor;
BEGIN
OPEN ref1 FOR SELECT 1;
RETURN NEXT ref1;
OPEN ref2 FOR SELECT 2, 3;
RETURN NEXT ref2;
RETURN;
END;
$BODY$
LANGUAGE plpgsql
Java Code:
CallableStatement cs = conn.prepareCall("{ call test() }");
ResultSet rs = cs.executeQuery();
while (rs.next()) {
System.out.println(rs.getString(1));
ResultSet rs2 = (ResultSet)rs.getObject(1);
while (rs2.next()) {
ResultSetMetaData rsmd = rs2.getMetaData();
int numberOfColumns = rsmd.getColumnCount();
System.out.println("numberOfColumns: " + numberOfColumns);
System.out.println(rs2.getString(1));
System.out.println(rs2.getString(2));
}
}
Output:
<unnamed portal 1>
numberOfColumns: 1
1
org.postgresql.util.PSQLException: The column index is out of range: 2, number of columns: 1.
at org.postgresql.jdbc2.AbstractJdbc2ResultSet.checkColumnIndex(AbstractJdbc2ResultSet.java:2680)
at org.postgresql.jdbc2.AbstractJdbc2ResultSet.checkResultSet(AbstractJdbc2ResultSet.java:2697)
at org.postgresql.jdbc2.AbstractJdbc2ResultSet.getString(AbstractJdbc2ResultSet.java:1872)
at PgBlob.test(PgBlob.java:64)
at PgBlob.main(PgBlob.java:37)
It appears the second result-set takes in the number of columns from the first irrespective of the number of columns from the second. If the change the function to return 2 refcursor's with same number of columns then it works as expected.
Function:
CREATE OR REPLACE FUNCTION test()
RETURNS SETOF refcursor AS
$BODY$
DECLARE
ref1 refcursor;
ref2 refcursor;
BEGIN
OPEN ref1 FOR SELECT 1, null;
RETURN NEXT ref1;
OPEN ref2 FOR SELECT 2, 3;
RETURN NEXT ref2;
RETURN;
END;
$BODY$
LANGUAGE plpgsql
Output:
<unnamed portal 1>
numberOfColumns: 2
1
4
<unnamed portal 2>
numberOfColumns: 2
2
3
