On Thu, Dec 10, 2015 at 7:38 AM, Corradini, Carlos
<CCorradini@correoargentino.com.ar> wrote:
> I have a Java application that must read a
> data provided by two ( 2 ) cursors returned by a function stored in a
> database. I know to retrieve data if the function have one ( 1 ) cursor, but
> with two I can’t. I will very pleased if any of you, in your free time of
> course, can explain me how, inside the java program, after connecting via
> jdbc to the database, I extract the data returned by the second cursor.
I will illustrate the principle of how a single function call can
return two cursors with psql, but you should be able to code up an
equivalent in Java, if you decide this is a good approach:
test=# create table feline (c text);
CREATE TABLE
test=# create table canine (c text);
CREATE TABLE
test=# insert into feline values ('lion'), ('tiger'), ('ocelot');
INSERT 0 3
test=# insert into canine values ('wolf'), ('jackal');
INSERT 0 2
test=# create or replace function twocursors(out f refcursor, out c
refcursor)
test-# language plpgsql
test-# as $$
test$# declare
test$# curs1 CURSOR FOR SELECT * FROM feline;
test$# curs2 CURSOR FOR SELECT * FROM canine;
test$# begin
test$# curs1 := 'feline';
test$# open curs1;
test$# f := curs1;
test$# curs2 := 'canine';
test$# open curs2;
test$# c := curs2;
test$# end;
test$# $$;
CREATE FUNCTION
test=# begin; select twocursors();
BEGIN
twocursors
-----------------
(feline,canine)
(1 row)
test=# fetch feline;
c
------
lion
(1 row)
test=# fetch canine;
c
------
wolf
(1 row)
test=# fetch feline;
c
-------
tiger
(1 row)
test=# fetch canine;
c
--------
jackal
(1 row)
test=# fetch feline;
c
--------
ocelot
(1 row)
I don't think you can get two ResultSet objects as cursors from a
single function call through JDBC in PostgreSQL, so you either need
to make two separate calls to get the two result sets (with the
usual dance to get those as cursors) and pull from the two
ResultSet objects as required, or use server-side fetches as
illustrated above. I would expect it to be much faster to use the
two result sets, since each fetch above would involve a round trip
from the client to PostgreSQL.
--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
