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