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

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