Re: [JDBC] plpgsql function with RETURNS SETOF refcursor in JAVA - Mailing list pgsql-general

Dear Mr. Adrian

Well, I must be honest, 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.

Many thanks !!!!

-----Mensaje original-----
De: Adrian Klaver [mailto:adrian.klaver@aklaver.com]
Enviado el: viernes, 11 de diciembre de 2015 12:27 p.m.
Para: Corradini, Carlos; pgsql-jdbc@postgresql.org;
pgsql-general@postgresql.org
CC: books@ejurka.com
Asunto: Re: [GENERAL] [JDBC] plpgsql function with RETURNS SETOF
refcursor in JAVA

On 12/11/2015 07:10 AM, Corradini, Carlos wrote:
> Mr. Adrian, first let me say many thanks for your replies, were very
> helpful for me. But, I must to say this other .....
>
> I take a copy from the function from the gui tool of pgadmin III
> called query sql, the original function name all the parameters, I do
> not know why this gui tool change that.

I have no I idea either, seems silly to me.

>
> You say that I am returning only one cursor, but I can say that I am
> doing exactly what are you explaining in your example, I am doing
> return next for each cursor to return.

 From your previous post:

CREATE OR REPLACE FUNCTION dw_bsc.proc_perspectives(
     character varying,
     integer,
     character varying,
     character varying,
     character varying,
     integer,
     date)
   RETURNS SETOF refcursor AS

....

You use two cursors internally, but are returning only one above. Unless
you do something like Kevin Grittner and I showed there is no way for
Postgres to know how to assign the internal cursors to return values
that can be used externally.

>
> I think I am doing something wrong in the java program but I can't
> realize what is this .....I will go now to see the examples in the
> URL's you did put below.
>
> Many thanks for all and excuse me for disturbing you and make you
> waste your free time in this.

Well the point of the list is to answer questions and if I did not have
the time or the interest I would not answer, so do not worry about it.

>
> Again, many thanks
>
> -----Mensaje original-----
> De: Adrian Klaver [mailto:adrian.klaver@aklaver.com]
> Enviado el: viernes, 11 de diciembre de 2015 11:37 a.m.
> Para: Corradini, Carlos; pgsql-jdbc@postgresql.org;
> pgsql-general@postgresql.org
> CC: books@ejurka.com
> Asunto: Re: [GENERAL] [JDBC] plpgsql function with RETURNS SETOF
> refcursor in JAVA
>
> On 12/11/2015 04:56 AM, Corradini, Carlos wrote:
>> Mr. Adrian, here i transcribe the code of the function
>
> Notes in line.
>
>>
>> -- Function: dw_bsc.proc_perspectives(character varying, integer,
>> character varying, character varying, character varying, integer,
> date)
>>
>> -- DROP FUNCTION dw_bsc.proc_perspectives(character varying, integer,

>> character varying, character varying, character varying, integer,
> date);
>>
>> CREATE OR REPLACE FUNCTION dw_bsc.proc_perspectives(
>>       character varying,
>>       integer,
>>       character varying,
>>       character varying,
>>       character varying,
>>       integer,
>>       date)
>>     RETURNS SETOF refcursor AS
>
> First you can name your function parameters:
>
> http://www.postgresql.org/docs/9.4/interactive/plpgsql-declarations.ht
> ml
> #PLPGSQL-DECLARATION-PARAMETERS
>
> 40.3.1. Declaring Function Parameters
>
>
> If you do that then you can simplify the below. In other words in
> above the first parameter becomes:
>
> v_oper varchar(1)
>
> Saves creating a NULL variable and assigning to it as below.
>
>> $BODY$
>> declare
>>
>>       v_oper varchar(1) := null;
>>       v_id integer := null;
>>       v_name varchar(50) := null;
>>       v_short_desc varchar(150) := null;
>>       v_descr varchar(500) := null;
>>       v_user_id integer := null;
>>       v_fecha date := null;
>>       v_resu integer := null;
>>       perspectives_cursor refcursor := null;
>>       goals_persps_cursor refcursor := null;
>>       null_cursor refcursor := null;
>>
>>
>> begin
>>
>>       v_oper := $1;
>>       v_id := $2;
>>       v_name := $3;
>>       v_short_desc := $4;
>>       v_descr := $5;
>>       v_user_id := $6;
>>       v_fecha := $7;
>>
>>
>>       -- oper R = READ
>>       -- oper D = DELETE
>>       -- oper M = UPDATE
>>       -- oper I = INSERT
>
>
> <SNIP>
>>
>> I can extract the data contained into cursor named
>> perspectives_cursor in the java application,  but the data caontained

>> into cursor named goals_persps_cursor not.
>
> Well you are only returning one refcursor, so that is all you are
> going to get. To return more than one cursor, modified example from
the docs:
>
> http://www.postgresql.org/docs/9.4/interactive/plpgsql-cursors.html
> See bottom of page for original example.
>
> CREATE OR REPLACE FUNCTION public.myfunc(cur_a refcursor, cur_b
> refcursor, arg_1 varchar(1))
>    RETURNS SETOF refcursor
>    LANGUAGE plpgsql
> AS $function$
> BEGIN
>       RAISE NOTICE 'arg_1 is %', arg_1;
>       OPEN cur_a FOR SELECT * FROM tbl_a;
>       RETURN NEXT $1;
>       OPEN cur_b FOR SELECT * FROM tbl_b;
>       RETURN NEXT $2;
> END;
> $function$
> ;
>
> test=> begin ;
> BEGIN
> test=> SELECT * FROM myfunc('a', 'b', '1');
> NOTICE:  arg_1 is 1
>    myfunc
> --------
>    a
>    b
> (2 rows)
>
> test=> fetch all from a;
>    fld_1
> -------
>        1
>        2
>        3
> (3 rows)
>
> test=> fetch all from b;
>    fld_1
> -------
>        4
>        5
>        6
> (3 rows)
>
>
> See this post from Kevin Grittner for an alternate method:
>
> http://www.postgresql.org/message-id/CACjxUsMy_zngFHBia+-QQuR8pOy87VU-
> L1
> E6HppWnDU2skjkVw@mail.gmail.com
>
> He also includes some notes on how to make this work with JDBC.
>
>>
>> This function was tested by this
>>
>>
>> begin;
>> select * from dw_bsc.proc_perspectives('R', 1, null, null, null,
>> null, null);
>>
>> fetch all from "<unnamed portal 17>"; end;
>>
>> inside the query gui tool provided by pgAdmin III
>>
>> The connection into the java application was changed to
>> con.setAutoCommit(false);
>>
>> I think I do not forget nothing else
>>
>> Some help will be appreciated very, very, very much !!!!!
>>
>>
>> -----Mensaje original-----
>> De: Adrian Klaver [mailto:adrian.klaver@aklaver.com]
>> Enviado el: jueves, 10 de diciembre de 2015 06:25 p.m.
>> Para: Corradini, Carlos; pgsql-jdbc@postgresql.org;
>> pgsql-general@postgresql.org
>> CC: books@ejurka.com
>> Asunto: Re: [GENERAL] [JDBC] plpgsql function with RETURNS SETOF
>> refcursor in JAVA
>>
>> On 12/10/2015 05:38 AM, Corradini, Carlos wrote:
>>> Dear Gurus :
>>>
>>>                    First let me say hello from Buenos Aires,
> Argentina.
>>> I took this emails addresses from internet ( page www.postgresql.org

>>> <http://www.postgresql.org> )
>>>
>>>                    Now I will try to explain which is my problem (
>>> excuse my poor level of English, please ). 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.
>>
>> Can you provide the function code that is returning the cursors?
>>
>>>
>>>                    Many thanks ( muchas gracias ) and I wait for
>>> yours replies as soon as you can.
>>>
>>
>>
>> --
>> Adrian Klaver
>> adrian.klaver@aklaver.com
>>
>
>


--
Adrian Klaver
adrian.klaver@aklaver.com