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

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-L1E6HppWnDU2skjkVw@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