Humair Mohammed <huma...@hotmail.com> writes: > 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.6JDBC4 Postgresql Driver, Version 9.0-801
> Function:CREATE OR REPLACE FUNCTION test() RETURNS SETOF refcursor > AS$BODY$DECLAREref1 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: > 11org.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$DECLAREref1 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: 214<unnamed portal > 2>numberOfColumns: 223 The example function works okay for me in psql. I think this is actually a question about how to deal with such cases through the JDBC driver, so I'd suggest asking on the pgsql-jdbc list. (Perhaps in a less messy format this time, and could we ask for a useful Subject: line too?) regards, tom lane -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql