Обсуждение: refcursor returned by pl/psql to jdbc

Ok, I wanted to wrap a select statement with a PL/pgsql procedure
(information hiding, etc.)
So I have:

CREATE OR REPLACE FUNCTION user_data_select_all(BIGINT)
RETURNS REFCURSOR AS '
DECLARE
    -- parameters
    owner_id_p ALIAS FOR $1;

    -- local variables
    rc REFCURSOR;
BEGIN
    OPEN rc FOR SELECT *
        FROM user_data
        WHERE
            owner_id = owner_id_p;
    RETURN rc;
END;
' LANGUAGE 'plpgsql' WITH (isstrict);

Now from within psql I get the following:

=> select user_data_select_all(12);
 user_data_select_all
----------------------
 <unnamed cursor 15>
(1 row)

I kind of expected to see the actual rows?!?

and from JDBC I get an exception:
Bad Long <unnamed cursor 1>
        at org.postgresql.jdbc2.ResultSet.toLong(ResultSet.java:1498)
        at org.postgresql.jdbc2.ResultSet.getLong(ResultSet.java:257)

So the question is can I wrap a select function as return a resultset to
JDBC?
Richard

See the new doc page I made for it:

    http://developer.postgresql.org/docs/postgres/plpgsql-cursors.html


The last section has the info you need.  This is now also referenced in
the FAQ on the web site.

---------------------------------------------------------------------------

Richard Emberson wrote:
> Ok, I wanted to wrap a select statement with a PL/pgsql procedure
> (information hiding, etc.)
> So I have:
>
> CREATE OR REPLACE FUNCTION user_data_select_all(BIGINT)
> RETURNS REFCURSOR AS '
> DECLARE
>     -- parameters
>     owner_id_p ALIAS FOR $1;
>
>     -- local variables
>     rc REFCURSOR;
> BEGIN
>     OPEN rc FOR SELECT *
>         FROM user_data
>         WHERE
>             owner_id = owner_id_p;
>     RETURN rc;
> END;
> ' LANGUAGE 'plpgsql' WITH (isstrict);
>
> Now from within psql I get the following:
>
> => select user_data_select_all(12);
>  user_data_select_all
> ----------------------
>  <unnamed cursor 15>
> (1 row)
>
> I kind of expected to see the actual rows?!?
>
> and from JDBC I get an exception:
> Bad Long <unnamed cursor 1>
>         at org.postgresql.jdbc2.ResultSet.toLong(ResultSet.java:1498)
>         at org.postgresql.jdbc2.ResultSet.getLong(ResultSet.java:257)
>
> So the question is can I wrap a select function as return a resultset to
> JDBC?
> Richard
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026