Re: [GENERAL] 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-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(dot)klaver(at)aklaver(dot)com]
> Enviado el: jueves, 10 de diciembre de 2015 06:25 p.m.
> Para: Corradini, Carlos; pgsql-jdbc(at)postgresql(dot)org;
> pgsql-general(at)postgresql(dot)org
> CC: books(at)ejurka(dot)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(dot)klaver(at)aklaver(dot)com
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com