Обсуждение: Returning multiple cursors/resultsets from PostgreSQL procedure

Hello,

Please forgive me if this has been answered a million
times before - I took a look around the FAQs and
postings but didn't see quite the same question.

I have an Oracle DB that I'd like to try converting to
PostgreSQL.  I have a J2EE application which calls
procedures in the Oracle database using
CallableStatements.  Some of my procedures use ref
cursors as out parameters, and some have multiple ref
cursor out parameters.  Here's an example procedure (a
bad but simple example):

CREATE OR REPLACE PACKAGE FOO

AS
    TYPE FooCursor IS REF CURSOR;

    PROCEDURE SELECT_FOO (
            foo_id IN VARCHAR2,
        foo_cursor1 OUT FooCursor,
        foo_cursor2 OUT FooCursor);
END FOO;

CREATE OR REPLACE PACKAGE BODY FOO

AS

    PROCEDURE SELECT_FOO (
            foo_id IN VARCHAR2,
        foo_cursor1 OUT FooCursor,
        foo_cursor2 OUT FooCursor)

    AS

    BEGIN
        OPEN foo_cursor1 FOR
        SELECT * FROM foo1;

        OPEN foo_cursor2 FOR
        SELECT * FROM foo2;

    END SELECT_FOO;

END FOO;

And in the J2EE app code, I have something like this:

cstmt = conn.prepareCall("{ call FOO.SELECT_FOO
(?,?,?) }");

cstmt.setString(1,id);
cstmt.registerOutParameter(2,OracleTypes.CURSOR);
cstmt.registerOutParameter(3,OracleTypes.CURSOR);

cstmt.execute();

rs1 = (ResultSet)cstmt.getObject(2);
// do rs stuff & close

rs2 = (ResultSet)cstmt.getObject(3);
// do rs stuff & close


My question is:  Using Postgre pgPL/SQL and the
PostgreSQL JDBC driver, is it possible to do this?

Thanks for any help you can provide.

Charles.

__________________________________
Do you Yahoo!?
The New Yahoo! Search - Faster. Easier. Bingo.
http://search.yahoo.com