Re: [GENERAL] plpgsql function with RETURNS SETOF refcursor in JAVA

Mr. Adrian, here i transcribe the code of the function

-- 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
$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

case v_oper
when 'R' then

if (v_id = 0) then

OPEN perspectives_cursor FOR
SELECT p.id, p.name, p.short_desc,
p.description
FROM dw_bsc.perspective p
order by p.name asc;
return next perspectives_cursor;
open goals_persps_cursor FOR select 'null' as
resultado2;
return next goals_persps_cursor;
else

OPEN perspectives_cursor FOR
SELECT p.name, p.short_desc, p.description
FROM dw_bsc.perspective p
WHERE P.ID = v_id;
return next perspectives_cursor;

OPEN goals_persps_cursor FOR
SELECT GP.ID, GP.DESCRIPTION
FROM DW_BSC.GOALS_PERSPECTIVE gp
WHERE GP.PER_ID = v_id;
return next goals_persps_cursor;

open null_cursor FOR select 'null' as
resultado3;
return next null_cursor;

end if;

when 'D' then

if (v_id = 0) then

open perspectives_cursor FOR select
'NULL' as resultado1;
return next perspectives_cursor;
open goals_persps_cursor FOR select
'null' as resultado2;
return next goals_persps_cursor;
open null_cursor FOR select 'null' as
resultado3;
return next null_cursor;
else

RAISE NOTICE 'Borrando GOALS ....';
delete from DW_BSC.GOALS_PERSPECTIVE gp
WHERE GP.PER_ID = v_id;
RAISE NOTICE 'Borrando PERSPECTIVE
....';
delete from DW_BSC.PERSPECTIVE p
WHERE P.ID = v_id;
open perspectives_cursor FOR select 'ok.
delete perspectives' as resultado1;
return next perspectives_cursor;
open goals_persps_cursor FOR select 'ok.
Delete goals' as resultado2;
return next goals_persps_cursor;
open null_cursor FOR select 'null' as
resultado3;
return next null_cursor;
end if;

when 'M' then

if (v_id = 0) then
RAISE NOTICE 'El id pasado al procedure
es null, imposible procesar UPDATE !!!';
open perspectives_cursor FOR select
'NULL' as resultado1;
return next perspectives_cursor;
open goals_persps_cursor FOR select
'null' as resultado2;
return next goals_persps_cursor;
open null_cursor FOR select 'null' as
resultado3;
return next null_cursor;
else

update DW_BSC.PERSPECTIVE p
set p.NAME = v_name,
p.DESCRIPTION = v_descr,
p.SHORT_DESC = v_short_desc,
p.USR_ID_UPD = v_user_id,
p.USR_DATE_UPD = v_fecha
where P.ID = v_id;
open perspectives_cursor FOR select 'ok.
update' as resultado1;
return next perspectives_cursor;
open goals_persps_cursor FOR select
'null' as resultado2;
return next goals_persps_cursor;
open null_cursor FOR select 'null' as
resultado3;
return next null_cursor;

end if;

when 'I' then

if (v_id = 0) then

RAISE NOTICE 'v_name : %', v_name;
RAISE NOTICE 'v_short_desc : %',
v_short_desc;
RAISE NOTICE 'v_descr : %', v_descr;
RAISE NOTICE 'v_user_id : %',v_user_id;
RAISE NOTICE 'v_fecha : %', v_fecha;
insert into dw_bsc.perspective
(name, short_desc, description,
usr_id_ins, usr_date_ins, usr_id_upd, usr_date_upd)
values
(v_name, v_short_desc, v_descr,
v_user_id, v_fecha, null, null);
open perspectives_cursor FOR select 'ok.
insert' as resultado1;
return next perspectives_cursor;
open goals_persps_cursor FOR select
'null' as resultado2;
return next goals_persps_cursor;
open null_cursor FOR select 'null' as
resultado3;
return next null_cursor;

else
RAISE NOTICE 'El id pasado al procedure
no es 0, imposible procesar INSERT !!!';
open perspectives_cursor FOR select
'NULL' as resultado1;
return next perspectives_cursor;
open goals_persps_cursor FOR select
'null' as resultado2;
return next goals_persps_cursor;
open null_cursor FOR select 'null' as
resultado3;
return next null_cursor;
end if;

end case;

end;
$BODY$
LANGUAGE plpgsql VOLATILE SECURITY DEFINER
COST 100
ROWS 1000;
ALTER FUNCTION dw_bsc.proc_perspectives(character varying, integer,
character varying, character varying, character varying, integer, date)
OWNER TO usr_dw_bsc_sys_adm;
GRANT EXECUTE ON FUNCTION dw_bsc.proc_perspectives(character varying,
integer, character varying, character varying, character varying,
integer, date) TO public;
GRANT EXECUTE ON FUNCTION dw_bsc.proc_perspectives(character varying,
integer, character varying, character varying, character varying,
integer, date) TO usr_dw_bsc_sys_adm;
GRANT EXECUTE ON FUNCTION dw_bsc.proc_perspectives(character varying,
integer, character varying, character varying, character varying,
integer, date) TO ro_dw_bsc_sys_adm;

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.

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