Received: from maia.hub.org (maia-3.hub.org [200.46.204.243]) by mail.postgresql.org (Postfix) with ESMTP id 8408B1337C0D for ; Wed, 20 Apr 2011 11:45:15 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.243]) (amavisd-maia, port 10024) with ESMTP id 23304-05 for ; Wed, 20 Apr 2011 14:45:08 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from col0-omc2-s2.col0.hotmail.com (col0-omc2-s2.col0.hotmail.com [65.55.34.76]) by mail.postgresql.org (Postfix) with ESMTP id 8F26D1337C07 for ; Wed, 20 Apr 2011 11:45:07 -0300 (ADT) Received: from COL115-W5 ([65.55.34.71]) by col0-omc2-s2.col0.hotmail.com with Microsoft SMTPSVC(6.0.3790.4675); Wed, 20 Apr 2011 07:45:07 -0700 Message-ID: Content-Type: multipart/alternative; boundary="_25a65d48-c4ab-4f14-addf-ecaba042ad4f_" X-Originating-IP: [65.192.185.2] From: Humair Mohammed To: Subject: Date: Wed, 20 Apr 2011 09:45:07 -0500 Importance: Normal MIME-Version: 1.0 X-OriginalArrivalTime: 20 Apr 2011 14:45:07.0484 (UTC) FILETIME=[8AC275C0:01CBFF69] X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.137 tagged_above=-5 required=5 tests=BAYES_00=-1.9, FREEMAIL_FROM=0.001, HTML_MESSAGE=0.001, RCVD_IN_DNSWL_NONE=-0.0001, RCVD_IN_SORBS_WEB=0.77, RFC_ABUSE_POST=0.001, T_RP_MATCHES_RCVD=-0.01 X-Spam-Level: X-Archive-Number: 201104/31 X-Sequence-Number: 35278 --_25a65d48-c4ab-4f14-addf-ecaba042ad4f_ Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable I am running into a behavior with a postgresql function with a SETOF refcur= sor's returning multiple columns. Not sure if there is a different way to r= etrieve a SETOF refcursor's with variable columns? Alternatively can I retu= rn a primitive value and a refcursor from the same function. I tried specif= ying this as OUT parameters without any luck. In Oracle you can pass this i= n functions: Platform:postgresql-x64-9.0 (PostgreSQL 9.0.1=2C compiled by Visual C++ bui= ld 1500=2C 64-bit)Java1.6JDBC4 Postgresql Driver=2C Version 9.0-801 Function:CREATE OR REPLACE FUNCTION test() RETURNS SETOF refcursor AS$BODY= $DECLAREref1 refcursor=3Bref2 refcursor=3BBEGIN OPEN ref1 FOR SELE= CT 1=3B RETURN NEXT ref1=3B OPEN ref2 FOR SELECT 2=2C 3=3B= RETURN NEXT ref2=3B RETURN=3BEND=3B $BODY$ LANGUAGE p= lpgsql Java Code:CallableStatement cs =3D conn.prepareCall("{ call test() }")=3BRe= sultSet rs =3D cs.executeQuery()=3B while (rs.next()) { System.out.println(rs.getString(1))=3B ResultSet rs2 = =3D (ResultSet)rs.getObject(1)=3B while (rs2.next()) { ResultSetMetaData r= smd =3D rs2.getMetaData()=3B int numberOfColumns =3D rsmd.getColumnCount()= =3B System.out.println("numberOfColumns: " + numberOfColumns)=3B System.o= ut.println(rs2.getString(1))=3B System.out.println(rs2.getString(2))=3B }} Output:numberOfColumns: 11org.postgresql.util.PSQLExcepti= on: The column index is out of range: 2=2C number of columns: 1. at = org.postgresql.jdbc2.AbstractJdbc2ResultSet.checkColumnIndex(AbstractJdbc2R= esultSet.java:2680) at org.postgresql.jdbc2.AbstractJdbc2ResultSet.c= heckResultSet(AbstractJdbc2ResultSet.java:2697) at org.postgresql.jd= bc2.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 fi= rst irrespective of the number of columns from the second. If the change th= e function to return 2 refcursor's with same number of columns then it work= s as expected. Function:CREATE OR REPLACE FUNCTION test() RETURNS SETOF refcursor AS$BODY= $DECLAREref1 refcursor=3Bref2 refcursor=3BBEGIN OPEN ref1 FOR SELE= CT 1=2C null=3B RETURN NEXT ref1=3B OPEN ref2 FOR SELECT 2= =2C 3=3B RETURN NEXT ref2=3B RETURN=3BEND=3B $BODY$ LA= NGUAGE plpgsql Output:numberOfColumns: 214numberOfColu= mns: 223 = --_25a65d48-c4ab-4f14-addf-ecaba042ad4f_ Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable

I am running into a beha= vior with a postgresql function with a SETOF refcursor's returning multiple= columns. Not sure if there is a different way to retrieve a SETOF refcurso= r's with variable columns? Alternatively can I return a primitive value and= a refcursor from the same function. I tried specifying this as OUT paramet= ers without any luck. In Oracle you can pass this in functions:

Platform:

postgresql-x64-9.0 (= PostgreSQL 9.0.1=2C compiled by Visual C++ build 1500=2C 64-bit)

Java1.6<= /font>

JDBC4 Postgres= ql Driver=2C Version 9.0-801

Function:

CREATE OR REPLACE FU= NCTION test()

=3B =3BRETURNS SETOF refcursor AS

$BODY$<= /div>

DECLA= RE

ref1 refcursor=3B

ref2 refcursor=3B

BEGIN =3B

=

=3B&n= bsp=3B =3B =3B =3B OPEN ref1 FOR SELECT 1=3B

=3B = =3B =3B =3B =3B RETURN NEXT ref1=3B =3B

=3B = =3B =3B =3B =3B OPEN ref2 FOR SELECT 2=2C 3=3B

<= div> =3B&nb= sp=3B =3B =3B =3B RETURN NEXT ref2=3B =3B =3B&nbs= p=3B =3B =3B =3B RETURN=3B

END=3B =3B =3B=

$BOD= Y$

=3B =3BLANGUAGE plpgsql

Java Code:

Callab= leStatement cs =3D conn.prepareCall("{ call test() }")=3B

= ResultSet rs = =3D cs.executeQuery()=3B

while (rs.next()) {

System.out.println(rs.getStri= ng(1))=3B

ResultSet rs2 =3D (ResultSet)rs.getObject(1)=3B

while (rs2.next()) {

=

ResultSetMetaD= ata rsmd =3D rs2.getMetaData()=3B

int numberOfColumns =3D rsmd.getColumnCo= unt()=3B

System.out.println("numberOfColumns: =3B" + numberOfColumns)= =3B

System.out.println(rs2.getString(1))=3B

System.out.println(rs2.getString(2))=3B=

}

}

Output:

<=3Bunnamed portal 1>=3B

numberOfColum= ns: 1

1

org.postgresql.util.PSQLException: The column i= ndex is out of range: 2=2C number of columns: 1.

=3B =3B= =3B =3B =3Bat org.postgresql.jdbc2.AbstractJdbc2ResultSet.c= heckColumnIndex(AbstractJdbc2ResultSet.java:2680)

=3B = =3B =3B =3B =3Bat org.postgresql.jdbc2.AbstractJdbc2ResultSe= t.checkResultSet(AbstractJdbc2ResultSet.java:2697)

=3B = =3B =3B =3B =3Bat org.postgresql.jdbc2.AbstractJdbc2ResultSe= t.getString(AbstractJdbc2ResultSet.java:1872)

=3B =3B &n= bsp=3B =3B =3Bat PgBlob.test(PgBlob.java:64)

=3B&n= bsp=3B =3B =3B =3Bat PgBlob.main(PgBlob.java:37)

It appears the second result-set takes in the number of columns from th= e first irrespective of the number of columns from the second. If the chang= e the function to return 2 refcursor's with same number of columns then it = works as expected.

Function:

CREATE OR REPLACE FUNCTION test()

=3B =3BRETURNS SETOF refcurso= r AS

$BOD= Y$

DECLAR= E

ref1 re= fcursor=3B

ref2 refcursor=3B

BEGIN =3B

=3B =3B =3B =3B =3B OPEN ref1 FOR= SELECT 1=2C null=3B

=3B =3B =3B =3B =3B RETURN NEXT ref1= =3B =3B

=3B =3B =3B =3B =3B OPEN ref2 FOR SELECT 2=2C 3= =3B

= =3B =3B =3B =3B =3B RETURN NEXT ref2=3B =3B

=3B =3B = =3B =3B =3B RETURN=3B

END=3B =3B =3B

$BODY$

=3B =3BLANGUAGE plpgsq= l

Output:

<=3Bunnamed portal 1>=3B

numberOfColum= ns: 2

1

4

<=3Bunnamed portal 2>=3B

<= div>numberOfColumns: 2

2

3

= --_25a65d48-c4ab-4f14-addf-ecaba042ad4f_--