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@xxxxxxxxxxx]
Enviado el: viernes, 11 de diciembre de 2015 11:37 a.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/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@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
