plpgsql function with RETURNS SETOF refcursor AS. How to get it work via JDBC - Mailing list pgsql-jdbc

Hi all,

  I'm already able to get Refcursor from a stored procedure.  But now I
need to get a SETOF refcursor and I can't make it work... Is that
possible to do this via JDBC?

He is the code I did.   The rsTmp.next() throws a  Connection is
closed.  Operation is not permitted. Exception.

    public ResultSet[] executePreparedStatementQueryMultipleCursor()
throws SQLException {
        ResultSet rsTmp = ps.executeQuery();
        ResultSet[] tempArray = new ResultSet[50];  // Should be enough
        int j = 0;
        while (rsTmp.next()) {
            tempArray[j] = (ResultSet) rsTmp.getObject(1);
            j++;
        }

        rs = new ResultSet[j];
        System.arraycopy(tempArray, 0, rs, 0, j);

        rsTmp.close();
        return rs;
    }


Here is a part of my function (see below) wich seems to work correctly.

If it's not supported is there a workaround?  Is this supposed to be
supported sooner?

Thanks for your help it's really appreciated!

/David




CREATE OR REPLACE FUNCTION usp_Comptabilite_JournalVentes(VARCHAR, DATE,
DATE, VARCHAR,VARCHAR) RETURNS SETOF refcursor  AS '
DECLARE
     companyId ALIAS FOR $1;
     startDate ALIAS FOR $2;
     endDate ALIAS FOR $3;
     periodIdFrom ALIAS FOR $4;
     periodIdTo ALIAS FOR $5;

     ref1 refcursor;
     ref2 refcursor;
     statement varchar(4000);
     appliedStr varchar(10);
     printedStr varchar(10);

BEGIN


....

  OPEN ref1 FOR EXECUTE statement;
  RETURN NEXT ref1;

 ...
  OPEN ref2 FOR EXECUTE statement;
  RETURN NEXT ref2;

  RETURN;


END;
' LANGUAGE 'plpgsql';