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

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.

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.

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.

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.html
#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