Yes, I was thinking to post my solution in this list for any who need to know how to receive more than one cursors from a function stored in a PostgreSQL database, but, living and working in Argentina, made an impossible work last week ( abnormal tasks to do are normal here !!!! ) , but now I have 5 minutes, then, I decided to post the java code.
The solution is very, very simple.... having a stored function with, for example, 2 input parameters and 2 cursors returned, I do not use RETURNS SETOF REFCURSORS in the function, but I have use the key OUT as out parameter ( just I use in Oracle Stored Procedures ) in the function, then, in the java program, after made the connection to the database, I have used the CallableStatement setting the value of all the inputs parameters with the values and type needed in the function, and as for the data returned ( in this case the cursors ), I have used the REGISTEROUTPARAMETER ( this method is provided by the calllablestatement java class ) with each value returned ( in this case the cursors ) numbered from 1 for the first out parameter and n for the last, informing the type TYPES.OTHER too. After made the java call ( callablestatement.execute() ), I set a public ( in this example ) variable as a RECORDSET and assigning each cursor to each RECORDSET variable : I use the integer variable called num_cursor for the first returned and num_cursor_02 for the second ( je .. I have broken my mind and brain thinking for the perfect names ... )..... rs = (ResultSet) cs.getObject(num_cursor); rs1 = (ResultSet) cs.getObject(num_cursor_02); the data obtained by the GETOBJECT method, being a cursor, must be parsed to a RECORDSET, and not, is very important this point, never use the name you gave to each cursor in the function stored, simply provide a number for which cursor want to put in each recordset variable..... after, using a java loop as "while recordset.next()" method, you can extract each value with getxxx(namedvariable) returned into each cursor and it's all ....... I hope I have been the most clear as my poor level of English could be.... Many thanks for all and specially to the postgresql community list !!!!!! -----Mensaje original----- De: Kevin Grittner [mailto:kgri...@gmail.com] Enviado el: viernes, 11 de diciembre de 2015 06:54 p.m. Para: Corradini, Carlos CC: Adrian Klaver; pgsql-j...@postgresql.org; pgsql-general@postgresql.org; Kris Jurka Asunto: Re: [GENERAL] [JDBC] plpgsql function with RETURNS SETOF refcursor in JAVA On Fri, Dec 11, 2015 at 2:11 PM, Corradini, Carlos <ccorrad...@correoargentino.com.ar> wrote: > with your and Mr. Kevin explanations, the Java program have worked > fine and have printed the data obtained from a two cursors inside a > PostgreSQL Database Stored Function. > > Then, I can confirm that this version of DB ( 9.4 ) use the OUT > parameter with refcursors and works fine. The JDBC interface provided > by the Server Postgresql can read the data inserted into these two > cursors via a callablestatement.registeroutparameter. For the benefit of others who may later have a similar problem and find this thread, it would be great if you could provide a little self-contained example of a Java program which uses the technique that you settled on. Thanks! -- Kevin Grittner EDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
