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.klaver@xxxxxxxxxxx]
Enviado el: jueves, 10 de diciembre de 2015 06:25 p.m.
Para: Corradini, Carlos; pgsql-jdbc@xxxxxxxxxxxxxx;
pgsql-general@xxxxxxxxxxxxxx
CC: books@xxxxxxxxxx
Asunto: Re: [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@xxxxxxxxxxx
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
