Thread: [JDBC] plpgsql function with RETURNS SETOF refcursor in JAVA
Dear Gurus :
First let me say hello from Buenos Aires, Argentina. I took this emails addresses from internet ( page 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.
Many thanks ( muchas gracias ) and I wait for yours replies as soon as you can.
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
On Thu, Dec 10, 2015 at 7:38 AM, Corradini, Carlos
<CCorradini@correoargentino.com.ar> wrote:
> 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.
I will illustrate the principle of how a single function call can
return two cursors with psql, but you should be able to code up an
equivalent in Java, if you decide this is a good approach:
test=# create table feline (c text);
CREATE TABLE
test=# create table canine (c text);
CREATE TABLE
test=# insert into feline values ('lion'), ('tiger'), ('ocelot');
INSERT 0 3
test=# insert into canine values ('wolf'), ('jackal');
INSERT 0 2
test=# create or replace function twocursors(out f refcursor, out c
refcursor)
test-# language plpgsql
test-# as $$
test$# declare
test$# curs1 CURSOR FOR SELECT * FROM feline;
test$# curs2 CURSOR FOR SELECT * FROM canine;
test$# begin
test$# curs1 := 'feline';
test$# open curs1;
test$# f := curs1;
test$# curs2 := 'canine';
test$# open curs2;
test$# c := curs2;
test$# end;
test$# $$;
CREATE FUNCTION
test=# begin; select twocursors();
BEGIN
twocursors
-----------------
(feline,canine)
(1 row)
test=# fetch feline;
c
------
lion
(1 row)
test=# fetch canine;
c
------
wolf
(1 row)
test=# fetch feline;
c
-------
tiger
(1 row)
test=# fetch canine;
c
--------
jackal
(1 row)
test=# fetch feline;
c
--------
ocelot
(1 row)
I don't think you can get two ResultSet objects as cursors from a
single function call through JDBC in PostgreSQL, so you either need
to make two separate calls to get the two result sets (with the
usual dance to get those as cursors) and pull from the two
ResultSet objects as required, or use server-side fetches as
illustrated above. I would expect it to be much faster to use the
two result sets, since each fetch above would involve a round trip
from the client to PostgreSQL.
--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
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
On 12/11/2015 07:10 AM, Corradini, Carlos wrote:
> Mr. Adrian, first let me say many thanks for your replies, were very
> helpful for me. But, I must to say this other .....
>
> I take a copy from the function from the gui tool of pgadmin III called
> query sql, the original function name all the parameters, I do not know
> why this gui tool change that.
I have no I idea either, seems silly to me.
>
> You say that I am returning only one cursor, but I can say that I am
> doing exactly what are you explaining in your example, I am doing return
> next for each cursor to return.
From your previous post:
CREATE OR REPLACE FUNCTION dw_bsc.proc_perspectives(
character varying,
integer,
character varying,
character varying,
character varying,
integer,
date)
RETURNS SETOF refcursor AS
....
You use two cursors internally, but are returning only one above. Unless
you do something like Kevin Grittner and I showed there is no way for
Postgres to know how to assign the internal cursors to return values
that can be used externally.
>
> I think I am doing something wrong in the java program but I can't
> realize what is this .....I will go now to see the examples in the URL's
> you did put below.
>
> Many thanks for all and excuse me for disturbing you and make you waste
> your free time in this.
Well the point of the list is to answer questions and if I did not have
the time or the interest I would not answer, so do not worry about it.
>
> Again, many thanks
>
> -----Mensaje original-----
> De: Adrian Klaver [mailto:adrian.klaver@aklaver.com]
> Enviado el: viernes, 11 de diciembre de 2015 11:37 a.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/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-L1
> E6HppWnDU2skjkVw@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
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
Mr. Adrian, first let me say many thanks for your replies, were very helpful for me. But, I must to say this other ..... I take a copy from the function from the gui tool of pgadmin III called query sql, the original function name all the parameters, I do not know why this gui tool change that. You say that I am returning only one cursor, but I can say that I am doing exactly what are you explaining in your example, I am doing return next for each cursor to return. I think I am doing something wrong in the java program but I can't realize what is this .....I will go now to see the examples in the URL's you did put below. Many thanks for all and excuse me for disturbing you and make you waste your free time in this. Again, many thanks -----Mensaje original----- De: Adrian Klaver [mailto:adrian.klaver@aklaver.com] Enviado el: viernes, 11 de diciembre de 2015 11:37 a.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/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-L1 E6HppWnDU2skjkVw@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
On Fri, Dec 11, 2015 at 2:11 PM, Corradini, Carlos <CCorradini@correoargentino.com.ar> wrote: > with your and Mr. Kevin explanations, the Java > program have worked fine and have printed the data obtained from a two > cursors inside a PostgreSQL Database Stored Function. > > Then, I can confirm that this version of DB ( 9.4 ) use the OUT > parameter with refcursors and works fine. The JDBC interface provided by > the Server Postgresql can read the data inserted into these two cursors > via a callablestatement.registeroutparameter. For the benefit of others who may later have a similar problem and find this thread, it would be great if you could provide a little self-contained example of a Java program which uses the technique that you settled on. Thanks! -- Kevin Grittner EDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
> I hope I have been the most clear as my poor level of English could be.. It would be great if you could express that in java + sql as well, so the exact code can be added to JDBC driver test suite as a regression test. Vladimir
>As I understand, it's all what you need, isn't you ???? Ideally I would like to see a pull request at https://github.com/pgjdbc/pgjdbc/pulls, however your code seems to be good enough so somebody else can pick it up, simplify a bit, and file a PR. Vladimir
>Ok. I understand, to put there a pull request, I must to register into this webpage ?? Exactly. Vladimir
Dear Mr. Adrian
Well, I must be honest, with your and Mr. Kevin explanations, the Java
program have worked fine and have printed the data obtained from a two
cursors inside a PostgreSQL Database Stored Function.
Then, I can confirm that this version of DB ( 9.4 ) use the OUT
parameter with refcursors and works fine. The JDBC interface provided by
the Server Postgresql can read the data inserted into these two cursors
via a callablestatement.registeroutparameter.
Many thanks !!!!
-----Mensaje original-----
De: Adrian Klaver [mailto:adrian.klaver@aklaver.com]
Enviado el: viernes, 11 de diciembre de 2015 12:27 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/11/2015 07:10 AM, Corradini, Carlos wrote:
> Mr. Adrian, first let me say many thanks for your replies, were very
> helpful for me. But, I must to say this other .....
>
> I take a copy from the function from the gui tool of pgadmin III
> called query sql, the original function name all the parameters, I do
> not know why this gui tool change that.
I have no I idea either, seems silly to me.
>
> You say that I am returning only one cursor, but I can say that I am
> doing exactly what are you explaining in your example, I am doing
> return next for each cursor to return.
From your previous post:
CREATE OR REPLACE FUNCTION dw_bsc.proc_perspectives(
character varying,
integer,
character varying,
character varying,
character varying,
integer,
date)
RETURNS SETOF refcursor AS
....
You use two cursors internally, but are returning only one above. Unless
you do something like Kevin Grittner and I showed there is no way for
Postgres to know how to assign the internal cursors to return values
that can be used externally.
>
> I think I am doing something wrong in the java program but I can't
> realize what is this .....I will go now to see the examples in the
> URL's you did put below.
>
> Many thanks for all and excuse me for disturbing you and make you
> waste your free time in this.
Well the point of the list is to answer questions and if I did not have
the time or the interest I would not answer, so do not worry about it.
>
> Again, many thanks
>
> -----Mensaje original-----
> De: Adrian Klaver [mailto:adrian.klaver@aklaver.com]
> Enviado el: viernes, 11 de diciembre de 2015 11:37 a.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/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.ht
> ml
> #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-
> L1
> E6HppWnDU2skjkVw@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
Yes, I was thinking to post my solution in this list for any who need to know how to receive more than one cursors from afunction stored in a PostgreSQL database, but, living and working in Argentina, made an impossible work last week ( abnormaltasks to do are normal here !!!! ) , but now I have 5 minutes, then, I decided to post the java code. The solution is very, very simple.... having a stored function with, for example, 2 input parameters and 2 cursors returned,I do not use RETURNS SETOF REFCURSORS in the function, but I have use the key OUT as out parameter ( just I usein Oracle Stored Procedures ) in the function, then, in the java program, after made the connection to the database, Ihave used the CallableStatement setting the value of all the inputs parameters with the values and type needed in the function,and as for the data returned ( in this case the cursors ), I have used the REGISTEROUTPARAMETER ( this method isprovided by the calllablestatement java class ) with each value returned ( in this case the cursors ) numbered from 1 forthe first out parameter and n for the last, informing the type TYPES.OTHER too. After made the java call ( callablestatement.execute()), I set a public ( in this example ) variable as a RECORDSET and assigning each cursor to eachRECORDSET variable : I use the integer variable called num_cursor for the first returned and num_cursor_02 for the second ( je .. I have brokenmy mind and brain thinking for the perfect names ... )..... rs = (ResultSet) cs.getObject(num_cursor); rs1 = (ResultSet) cs.getObject(num_cursor_02); the data obtained by the GETOBJECT method, being a cursor, must be parsed to a RECORDSET, and not, is very important thispoint, never use the name you gave to each cursor in the function stored, simply provide a number for which cursor wantto put in each recordset variable..... after, using a java loop as "while recordset.next()" method, you can extract eachvalue with getxxx(namedvariable) returned into each cursor and it's all ....... I hope I have been the most clear as my poor level of English could be.... Many thanks for all and specially to the postgresql community list !!!!!! -----Mensaje original----- De: Kevin Grittner [mailto:kgrittn@gmail.com] Enviado el: viernes, 11 de diciembre de 2015 06:54 p.m. Para: Corradini, Carlos CC: Adrian Klaver; pgsql-jdbc@postgresql.org; pgsql-general@postgresql.org; Kris Jurka Asunto: Re: [GENERAL] [JDBC] plpgsql function with RETURNS SETOF refcursor in JAVA On Fri, Dec 11, 2015 at 2:11 PM, Corradini, Carlos <CCorradini@correoargentino.com.ar> wrote: > with your and Mr. Kevin explanations, the Java program have worked > fine and have printed the data obtained from a two cursors inside a > PostgreSQL Database Stored Function. > > Then, I can confirm that this version of DB ( 9.4 ) use the OUT > parameter with refcursors and works fine. The JDBC interface provided > by the Server Postgresql can read the data inserted into these two > cursors via a callablestatement.registeroutparameter. For the benefit of others who may later have a similar problem and find this thread, it would be great if you could providea little self-contained example of a Java program which uses the technique that you settled on. Thanks! -- Kevin Grittner EDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
I forgot one more thing ... The return I made in the stored function is : RETURNS RECORD AS ' Excuse me for the forget ....... -----Mensaje original----- De: Kevin Grittner [mailto:kgrittn@gmail.com] Enviado el: viernes, 11 de diciembre de 2015 06:54 p.m. Para: Corradini, Carlos CC: Adrian Klaver; pgsql-jdbc@postgresql.org; pgsql-general@postgresql.org; Kris Jurka Asunto: Re: [GENERAL] [JDBC] plpgsql function with RETURNS SETOF refcursor in JAVA On Fri, Dec 11, 2015 at 2:11 PM, Corradini, Carlos <CCorradini@correoargentino.com.ar> wrote: > with your and Mr. Kevin explanations, the Java program have worked > fine and have printed the data obtained from a two cursors inside a > PostgreSQL Database Stored Function. > > Then, I can confirm that this version of DB ( 9.4 ) use the OUT > parameter with refcursors and works fine. The JDBC interface provided > by the Server Postgresql can read the data inserted into these two > cursors via a callablestatement.registeroutparameter. For the benefit of others who may later have a similar problem and find this thread, it would be great if you could providea little self-contained example of a Java program which uses the technique that you settled on. Thanks! -- Kevin Grittner EDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
Well Vladimir, i copy here only the part used in the java program to connect, set the parameters and execute the call
tothe stored function ( I show how I manage both oracle and postgresql, but advises to better programming are welcome
!!!!):
1) connecting, setting the parameters and execute the CallableStatement
con = DriverManager.getConnection(DB_URL+DB_NAME, clsUserName, clsUserPass);
switch(DBAccess) {
case "ORACLE":
num_cursor = 8;
num_cursor_02 = 9;
call_proc_DB = "{call dw_bsc.proc_perspectives(?, ?, ?, ?, ?, ?, ?, ?, ?, ?)}";
cs = con.prepareCall(call_proc_DB);
cs.setString(1, operac);
cs.setInt(2, pId);
cs.setNull(3, java.sql.Types.NULL);
cs.setNull(4, java.sql.Types.NULL);
cs.setNull(5, java.sql.Types.NULL);
cs.setNull(6, java.sql.Types.NULL);
cs.setNull(7, java.sql.Types.NULL);
cs.registerOutParameter(8, OracleTypes.CURSOR);
cs.registerOutParameter(9, OracleTypes.CURSOR);
cs.registerOutParameter(10, OracleTypes.INTEGER);
break;
case "POSTGRE":
num_cursor = 1;
num_cursor_02 = 2;
call_proc_DB = "{call dw_bsc.proc_perspectives(?, ?, ?, ?, ?, ?, ?)}";
con.setAutoCommit(false);
cs = con.prepareCall(call_proc_DB);
cs.setString(1, operac);
cs.setInt(2, pId);
cs.setNull(3, java.sql.Types.VARCHAR);
cs.setNull(4, java.sql.Types.VARCHAR);
cs.setNull(5, java.sql.Types.VARCHAR);
cs.setNull(6, java.sql.Types.INTEGER);
cs.setNull(7, java.sql.Types.DATE);
cs.registerOutParameter(1, Types.OTHER);
cs.registerOutParameter(2, Types.OTHER);
break;
}
cs.execute();
rs = (ResultSet) cs.getObject(num_cursor);
rs1 = (ResultSet) cs.getObject(num_cursor_02);
2) looping for extract the data in the cursors
try {
while(rs.next()) {
txtPerspName.setText(rs.getString("name"));
taShortDesc.setText(rs.getString("short_desc"));
taDescription.setText(rs.getString("description"));
}
operac = "M";
while(rs1.next()) {
tblGoalsPerspModel.addRow(new Object[tblPerspGoals.getRowCount()]);
tblGoalsPerspModel.setValueAt((tblPerspGoals.getRowCount()), (tblPerspGoals.getRowCount()-1),
0);
tblGoalsPerspModel.setValueAt(rs1.getString("id"), (tblPerspGoals.getRowCount()-1), 1);
tblGoalsPerspModel.setValueAt(rs1.getString("description"), (tblPerspGoals.getRowCount()-1),
2);
}
} catch (SQLException sqle) {
Logger.getLogger(frmSetPerspectives.class.getName()).log(Level.SEVERE, null, sqle);
} catch (Exception ex) {
hacer_igual = "I";
}
3) the sql postgre code for the stored function I have created
CREATE OR REPLACE FUNCTION dw_bsc.proc_perspectives(
IN character varying,
IN integer,
IN character varying,
IN character varying,
IN character varying,
IN integer,
IN date,
OUT c1 refcursor,
OUT c2 refcursor)
RETURNS record 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 := 'perspectives_cursor';
goals_persps_cursor refcursor := 'goals_persps_cursor';
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
-- begin
if (v_id = 0) then
RAISE NOTICE 'Estoy en el select sin filtros';
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;
c1 := perspectives_cursor;
open goals_persps_cursor FOR select 'null' as resultado2;
-- return next goals_persps_cursor;
c2 := goals_persps_cursor;
else
RAISE NOTICE 'Estoy en el select de un solo id';
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;
c1 := 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;
c2 := goals_persps_cursor;
end if;
-- end;
when 'D' then
-- begin
if (v_id = 0) then
RAISE NOTICE 'El id pasado al procedure es null, imposible procesar DELETE!!!';
open perspectives_cursor FOR select 'NULL' as resultado1;
c1 := perspectives_cursor;
-- return next perspectives_cursor;
open goals_persps_cursor FOR select 'null' as resultado2;
c2 := goals_persps_cursor;
-- return next goals_persps_cursor;
else
RAISE NOTICE 'Estoy en el delete con id : %', v_id;
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;
c1 := perspectives_cursor;
-- return next perspectives_cursor;
open goals_persps_cursor FOR select 'ok. Delete goals' as resultado2;
c2 := goals_persps_cursor;
-- return next goals_persps_cursor;
end if;
-- end;
when 'M' then
-- begin
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;
c1 := perspectives_cursor;
-- return next perspectives_cursor;
open goals_persps_cursor FOR select 'null' as resultado2;
c2 := goals_persps_cursor;
-- return next goals_persps_cursor;
else
RAISE NOTICE 'Estoy en el update con id : %', v_id;
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;
c1 := perspectives_cursor;
-- return next perspectives_cursor;
open goals_persps_cursor FOR select 'null' as resultado2;
c2 := goals_persps_cursor;
-- return next goals_persps_cursor;
end if;
-- end;
when 'I' then
-- begin
if (v_id = 0) then
RAISE NOTICE 'Estoy en el insert con id : %', v_id;
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;
c1 := perspectives_cursor;
-- return next perspectives_cursor;
open goals_persps_cursor FOR select 'null' as resultado2;
c2 := goals_persps_cursor;
-- return next goals_persps_cursor;
else
RAISE NOTICE 'El id pasado al procedure no es 0, imposible procesar INSERT !!!';
open perspectives_cursor FOR select 'NULL' as resultado1;
c1 := perspectives_cursor;
-- return next perspectives_cursor;
open goals_persps_cursor FOR select 'null' as resultado2;
c2 := goals_persps_cursor;
-- return next goals_persps_cursor;
end if;
-- exception
-- when others then
-- raise notice 'Se dio el error número %',sqlstate,' con descripción %', sqlerrm;
-- end;
end case;
end;
$BODY$
LANGUAGE plpgsql VOLATILE SECURITY DEFINER
COST 100;
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,
charactervarying, integer, date) TO public;
GRANT EXECUTE ON FUNCTION dw_bsc.proc_perspectives(character varying, integer, character varying, character varying,
charactervarying, integer, date) TO usr_dw_bsc_sys_adm;
GRANT EXECUTE ON FUNCTION dw_bsc.proc_perspectives(character varying, integer, character varying, character varying,
charactervarying, integer, date) TO ro_dw_bsc_sys_adm;
One more thing, I am a DBA ORACLE and not a Developer, please, excuse me if I made "horrors" in the programming, I
promiseto be more effective next time !!!!!
As I understand, it's all what you need, isn't you ????
-----Mensaje original-----
De: Vladimir Sitnikov [mailto:sitnikov.vladimir@gmail.com]
Enviado el: lunes, 14 de diciembre de 2015 10:20 a.m.
Para: Corradini, Carlos
CC: Kevin Grittner; Adrian Klaver; List; pgsql-general@postgresql.org; Kris Jurka
Asunto: Re: [GENERAL] [JDBC] plpgsql function with RETURNS SETOF refcursor in JAVA
> I hope I have been the most clear as my poor level of English could be..
It would be great if you could express that in java + sql as well, so the exact code can be added to JDBC driver test
suiteas a regression test.
Vladimir
Ok. I understand, to put there a pull request, I must to register into this webpage ?? -----Mensaje original----- De: Vladimir Sitnikov [mailto:sitnikov.vladimir@gmail.com] Enviado el: lunes, 14 de diciembre de 2015 10:53 a.m. Para: Corradini, Carlos CC: Kevin Grittner; Adrian Klaver; List; pgsql-general@postgresql.org; Kris Jurka Asunto: Re: [GENERAL] [JDBC] plpgsql function with RETURNS SETOF refcursor in JAVA >As I understand, it's all what you need, isn't you ???? Ideally I would like to see a pull request at https://github.com/pgjdbc/pgjdbc/pulls, however your code seems to be goodenough so somebody else can pick it up, simplify a bit, and file a PR. Vladimir