Обсуждение: BUG #5753: Existing Functions No Longer Work

The following bug has been logged online:

Bug reference:      5753
Logged by:          Vince Maxey
Email address:      vamax27@yahoo.com
PostgreSQL version: 9.0
Operating system:   Windows
Description:        Existing Functions No Longer Work
Details:

Hi,

Recently I upgraded a personal application built a number of years ago,
including java, eclipse, struts and postgresql and now face an issue with
postgresql in that application functions no longer work, specfically as
related to refcursors.  The original application was based on postgresql 8.4
I believe.

I have pgAdminIII installed and have been searching hi and low on the
Internet for others who may have faced similar issues; and I cannot find any
documentation which indicates specifically how to write a function (if the
syntax has changed for 9.0).

Here is a test set up I have created:

CREATE TABLE test_user
(
userid int4 NOT NULL,
userlogin varchar(20) NOT NULL,
userlevel int2 DEFAULT 0,
activeflag bool DEFAULT true,
username varchar(50),
enteredby int4,
CONSTRAINT test_user_pk PRIMARY KEY (userid)
)
WITH OIDS;
ALTER TABLE test_user OWNER TO postgres;
GRANT ALL ON TABLE test_user TO postgres;
GRANT ALL ON TABLE test_user TO public;


insert into test_user values (1,'A',1,'f','test1',1);
insert into test_user values (2,'B',1,'t','test2',1);
insert into test_user values (3,'C',1,'t','test3',2);
insert into test_user values (4,'Ad',1,'f','test4',1);

CREATE OR REPLACE FUNCTION test_proc(bigint)
RETURNS refcursor AS
$BODY$
DECLARE

userinfo refcursor;

BEGIN

open userinfo for

select * from test_user where userid = $1;

return userinfo;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
ALTER FUNCTION test_proc(bigint) OWNER TO postgres;
GRANT EXECUTE ON FUNCTION test_proc(bigint) TO public;
GRANT EXECUTE ON FUNCTION test_proc(bigint) TO postgres;

This SQL works fine: select * from test_user where userid = 2;

But when I try to call the function: select test_proc(2); I get a column
header: test_proc refcursor and the value in this column is simply: <unnamed
portal n>, where n seems to indicate how many times I have run a cursor from
the SQL window.

I also created this function:

CREATE OR REPLACE FUNCTION test_proc1(bigint)
RETURNS int4 AS
$BODY$
DECLARE

rec int4;

BEGIN

select enteredby into rec from test_user where userid = $1;
return rec;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
ALTER FUNCTION test_proc1(bigint) OWNER TO postgres;
GRANT EXECUTE ON FUNCTION test_proc1(bigint) TO public;
GRANT EXECUTE ON FUNCTION test_proc1(bigint) TO postgres;

These statements return correct values: select test_proc1(2); or select
test_proc1(3);

I used refcursors throughout my application and was surprised to be having
issues with them simply for moving to 9.0

Has this issue been encountered and already addressed? and/or is there
documentation in regard to modifying functions in relation to this release?

I did see something in regard to %rowtype, but I have numerous queries based
on multiple table joins and it doesn't make sense that I should have to
define datatypes for every individual query...

Any help or direction to existing documentation would be greatly
appreciated.

thank you very much!

"Vince Maxey" <vamax27@yahoo.com> writes:
> Recently I upgraded a personal application built a number of years ago,
> including java, eclipse, struts and postgresql and now face an issue with
> postgresql in that application functions no longer work, specfically as
> related to refcursors.  The original application was based on postgresql 8.4
> I believe.
> ...
> But when I try to call the function: select test_proc(2); I get a column
> header: test_proc refcursor and the value in this column is simply: <unnamed
> portal n>, where n seems to indicate how many times I have run a cursor from
> the SQL window.

The example you give acts exactly as I would expect, ie, it returns the
generated name of a cursor.  And it does so in every release back to at
least 8.0, not just 9.0.  So I think you've simplified your example to
the point that it no longer demonstrates whatever problem you're
actually having.

            regards, tom lane

Thanks for your response, Tom.

I guess my question would be, what needs to change in my syntax to expect t=
o get=20
one row returned?

Here are a couple of examples that do work in my existing application prior=
 to=20
my recent computer switch and re-build=A0(and I have well over 100 of these=
 types=20
of functions defined, some more complex than others, but I figured a simple=
=20
example would help someone else to most easily be able to help me).


-- Function: dimension.get_location_holiday(bigint)
-- DROP FUNCTION dimension.get_location_holiday(bigint);
CREATE OR REPLACE FUNCTION dimension.get_location_holiday(bigint)
=A0 RETURNS refcursor AS
$BODY$=20
DECLARE
=A0
=A0loc refcursor;
BEGIN
=A0open loc for=20
=A0=A0select * from dimension.location_holiday where holidayid =3D $1;=A0
=A0return loc;
END;
$BODY$
=A0 LANGUAGE plpgsql VOLATILE
=A0 COST 100;
ALTER FUNCTION dimension.get_location_holiday(bigint) OWNER TO postgres;
GRANT EXECUTE ON FUNCTION dimension.get_location_holiday(bigint) TO public;
GRANT EXECUTE ON FUNCTION dimension.get_location_holiday(bigint) TO postgre=
s;


-- Function: dimension.get_location_list(character varying, character varyi=
ng,=20
integer)
-- DROP FUNCTION dimension.get_location_list(character varying, character=
=20
varying, integer);
CREATE OR REPLACE FUNCTION dimension.get_location_list(character varying,=
=20
character varying, integer)
=A0 RETURNS refcursor AS
$BODY$=20
DECLARE
=A0loc refcursor;
BEGIN
=A0IF $3 =3D 1 THEN
=A0=A0open loc for =A0=A0
=A0=A0select a.locationid, a.locationname, a.partnerid, b.partnername,=20
a.phone1,a.phone2,=A0=A0=20

=A0=A0a.fax1, a.fax2,a.physaddress1, a.physaddress2, a.physcityid, e.city||=
',=20
'||e.statecode||'=A0 '||e.zipcode,
=A0=A0a.contact1, a.contact2, a.email1, a.email2, a.activestatus, a.timezon=
e,=20
a.taxrate, e.statecode,a.faxflag,
=A0=A0a.ticklerflag,case when a.ticklerflag =3D 't' then 'YES' else 'NO' en=
d, e.city
=A0=A0from dimension.location_base a, dimension.partner b, postal.us_zip e=
=20
=A0=A0where a.partnerid =3D b.partnerid and a.physcityid =3D e.zipid and e.=
statecode =3D=20
$2 order by a.locationname;
=A0ELSE
=A0=A0IF $3 =3D 0 THEN
=A0=A0=A0open loc for =A0=A0
=A0=A0=A0select a.locationid, a.locationname, a.partnerid, b.partnername,=
=20
a.phone1,a.phone2,=A0=A0=20

=A0=A0=A0a.fax1, a.fax2,a.physaddress1, a.physaddress2, a.physcityid, e.cit=
y||',=20
'||e.statecode||'=A0 '||e.zipcode,
=A0=A0=A0a.contact1, a.contact2, a.email1, a.email2, a.activestatus, a.time=
zone,=20
a.taxrate, e.statecode,a.faxflag,
=A0=A0=A0a.ticklerflag,case when a.ticklerflag =3D 't' then 'YES' else 'NO'=
 end, e.city
=A0=A0=A0from dimension.location_base a, dimension.partner b, postal.us_zip=
 e=20
=A0=A0=A0where a.partnerid =3D b.partnerid and a.physcityid =3D e.zipid and=
 e.statecode =3D=20
$2=20

=A0=A0=A0and lower(a.locationname) like $1||'%' order by a.locationname;=A0
=A0=A0ELSE
=A0=A0=A0open loc for =A0=A0
=A0=A0=A0select a.locationid, a.locationname, a.partnerid, b.partnername,=
=20
a.phone1,a.phone2,=A0=A0=20

=A0=A0=A0a.fax1, a.fax2,a.physaddress1, a.physaddress2, a.physcityid, e.cit=
y||',=20
'||e.statecode||'=A0 '||e.zipcode,
=A0=A0=A0a.contact1, a.contact2, a.email1, a.email2, a.activestatus, a.time=
zone,=20
a.taxrate, e.statecode,a.faxflag,
=A0=A0=A0a.ticklerflag,case when a.ticklerflag =3D 't' then 'YES' else 'NO'=
 end, e.city
=A0=A0=A0from dimension.location_base a, dimension.partner b, postal.us_zip=
 e=20
=A0=A0=A0where a.partnerid =3D b.partnerid and a.physcityid =3D e.zipid and=
 a.partnerid =3D=20
$1;
=A0
=A0=A0END IF;
=A0END IF;
=A0return loc;
END;
$BODY$
=A0 LANGUAGE plpgsql VOLATILE
=A0 COST 100;
ALTER FUNCTION dimension.get_location_list(character varying, character var=
ying,=20
integer) OWNER TO postgres;
GRANT EXECUTE ON FUNCTION dimension.get_location_list(character varying,=20
character varying, integer) TO public;
GRANT EXECUTE ON FUNCTION dimension.get_location_list(character varying,=20
character varying, integer) TO postgres;
GRANT EXECUTE ON FUNCTION dimension.get_location_list(character varying,=20
character varying, integer) TO "eMenuAdmin";


I am running my test procs from the pgAdminIII GUI.=A0 Is my syntax wrong t=
o=20
execute the function?=A0 If I run select test_proc1(3), I do get the correc=
t=20
result which is a column header (test_proc1 integer) and a value (2).=A0 So=
 why=20
wouldn't I get a 6-column result set when running select test_proc(2) ?

My java code=A0syntax is as follows:

=A0public Collection getLocationList(String pname, String ste, int type) {=
=A0=A0=A0
=A0=A0PartnerDAO ef =3D new PartnerDAO();
=A0=A0CallableStatement proc =3D null;
=A0=A0Connection conn =3D ef.getConnection();
=A0=A0Collection locations =3D new ArrayList();=A0=A0=A0=A0=A0=A0
=A0=A0try {=A0=A0=A0
=A0=A0=A0proc =3D conn.prepareCall("{ ?=3D call dimension.get_location_list=
(?,?,?) }");
=A0=A0=A0proc.registerOutParameter(1, Types.OTHER);
=A0=A0=A0=A0=A0 proc.setString(2, pname.toLowerCase().trim());
=A0=A0=A0=A0=A0 proc.setString(3, ste);
=A0=A0=A0=A0=A0 proc.setInt(4, type);=A0=A0=20
=A0=A0=A0=A0=A0 conn.setAutoCommit(false);
=A0=A0=A0=A0=A0 proc.execute();=A0=A0=A0=A0 =A0
=A0=A0=A0=A0=A0 ResultSet rs =3D (ResultSet) proc.getObject(1);
=A0=A0=A0=A0=A0 while (rs.next()) {
=A0=A0=A0=A0=A0 =A0LocationVO eRec =3D new LocationVO();=A0=A0=A0=A0=A0 =A0=
=A0=A0=A0=A0=A0 =A0
=A0=A0=A0=A0=A0 =A0eRec.setLocationId(rs.getInt(1));
=A0=A0=A0=A0=A0 =A0eRec.setLocationName(rs.getString(2));
=A0=A0=A0=A0=A0 =A0eRec.setPartnerId(rs.getInt(3));
=A0=A0=A0=A0eRec.setPartnerName(rs.getString(4));
=A0=A0=A0=A0eRec.setPhone1(rs.getString(5));
=A0=A0=A0=A0eRec.setDbphone1(rs.getString(5));
=A0=A0=A0=A0eRec.setPhone2(rs.getString(6));
=A0=A0=A0=A0eRec.setDbphone2(rs.getString(6));
=A0=A0=A0=A0eRec.setFax1(rs.getString(7));
=A0=A0=A0=A0eRec.setDbfax1(rs.getString(7));
=A0=A0=A0=A0eRec.setFax2(rs.getString(8));
=A0=A0=A0=A0eRec.setDbfax2(rs.getString(8));
=A0=A0=A0=A0eRec.setAddress1(rs.getString(9));
=A0=A0=A0=A0eRec.setAddress2(rs.getString(10));
=A0=A0=A0=A0eRec.setCityId(rs.getInt(11));
=A0=A0=A0=A0eRec.setCityName(rs.getString(12));
=A0=A0=A0=A0eRec.setContact1(rs.getString(13));
=A0=A0=A0=A0eRec.setDbcontact1(rs.getString(13));
=A0=A0=A0=A0eRec.setContact2(rs.getString(14));
=A0=A0=A0=A0eRec.setDbcontact2(rs.getString(14));
=A0=A0=A0=A0eRec.setEmail1(rs.getString(15));
=A0=A0=A0=A0eRec.setDbemail1(rs.getString(15));
=A0=A0=A0=A0eRec.setEmail2(rs.getString(16));
=A0=A0=A0=A0eRec.setDbemail2(rs.getString(16));
=A0=A0=A0=A0eRec.setStatus(rs.getInt(17));
=A0=A0=A0=A0eRec.setDbstatus(rs.getString(17));
=A0=A0=A0=A0eRec.setTimeZone(rs.getString(18));
=A0=A0=A0=A0eRec.setTaxRate(rs.getDouble(19));
=A0=A0=A0=A0eRec.setDbtaxRate(rs.getDouble(19));
=A0=A0=A0=A0eRec.setStateCode(rs.getString(20));
=A0=A0=A0=A0eRec.setFaxFlag(Boolean.parseBoolean(rs.getString(21)));
=A0=A0=A0=A0eRec.setDbfaxFlag(Boolean.parseBoolean(rs.getString(21)));
=A0=A0=A0=A0eRec.setTicklerFlag(Boolean.parseBoolean(rs.getString(22)));
=A0=A0=A0=A0eRec.setTicklerFlagText(rs.getString(23));
=A0=A0=A0=A0eRec.setScName(rs.getString(24));
=A0=A0=A0=A0eRec.setCopyMenuSourceId(0);
=A0=A0=A0=A0=A0 =A0locations.add(eRec);=A0=A0=A0=A0
=A0=A0=A0=A0=A0 }=A0=A0=A0
=A0=A0
=A0=A0} catch (Exception e) {
=A0=A0=A0e.printStackTrace();
=A0=A0}finally {
=A0=A0=A0clearResources(conn, proc);
=A0=A0}
=A0=A0return locations;=A0=A0
=A0}

If I am not including something specific required to actually display a res=
ult=20
set, can you enlighten me?=A0 How would you write a function to return a ro=
w from=20
the test data I provided?=A0 I'm stumped.

Sincerely,

Vince Maxey



----- Original Message ----
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Vince Maxey <vamax27@yahoo.com>
Cc: pgsql-bugs@postgresql.org
Sent: Sat, November 13, 2010 1:03:46 PM
Subject: Re: [BUGS] BUG #5753: Existing Functions No Longer Work

"Vince Maxey" <vamax27@yahoo.com> writes:
> Recently I upgraded a personal application built a number of years ago,
> including java, eclipse, struts and postgresql and now face an issue with
> postgresql in that application functions no longer work, specfically as
> related to refcursors.=A0 The original application was based on postgresq=
l 8.4
> I believe.
> ...
> But when I try to call the function: select test_proc(2); I get a column
> header: test_proc refcursor and the value in this column is simply: <unna=
med
> portal n>, where n seems to indicate how many times I have run a cursor f=
rom
> the SQL window.

The example you give acts exactly as I would expect, ie, it returns the
generated name of a cursor.=A0 And it does so in every release back to at
least 8.0, not just 9.0.=A0 So I think you've simplified your example to
the point that it no longer demonstrates whatever problem you're
actually having.

=A0=A0=A0 =A0=A0=A0 =A0=A0=A0 regards, tom lane

vince maxey <vamax27@yahoo.com> writes:
> I am running my test procs from the pgAdminIII GUI.  Is my syntax wrong to
> execute the function?  If I run select test_proc1(3), I do get the correct
> result which is a column header (test_proc1 integer) and a value (2).  So why
> wouldn't I get a 6-column result set when running select test_proc(2) ?

Because those functions return the name of a cursor, not the result of
fetching from the cursor.  The same would have been true in any prior
release too, so I'm not sure why you're having trouble with 9.0 and
not before.  You sure you haven't dropped a level of indirect fetch
from your client code?

            regards, tom lane

Tom, or anyone else working with this dB, can you respond to my question:

How should the syntax for a function be formulated to return a refcursor=20
containing one or more records?=A0=20


I have many years SQL development experience and work with Oracle in my cur=
rent=20
position.=A0 I'm not a novice programmer.

My functions all worked prior to switching to 9.0 and I can excute function=
s=20
from the pgAdminIII UI which return an individual result, such as an intege=
r,=20
but cannot test/troubleshoot those which use refcursors in the same manner;=
 and=20
they are not working within my application.=A0 They used to work.

I've provided test data and functions for your inspection and validation.=
=A0 Even=20
pointing me to some substantial documentation (white paper or actual book) =
that=20
contains bonafide examples of how to write postgresql functions would proba=
bly=20
help.=A0 But simply providing syntax segments is not working,=A0 I've not c=
ome=20
across any examples that I can translate or compare with my existing effort=
s.

According to your documentation, new releases should be backward compatible=
;=20
other than for specific elements.=A0 I would think this particular function=
ality=20
should be backward compatible but as I'm finding it not to be, please take =
some=20
time to investigate and validate for yourselves what I have communicated.

I really do think postgreSQL is a great database from a development=20
perspective.=A0 If I can get over this issue, perhaps I can provide some=20
documentation which others can use to create their own functions.

thank you for your assistance.


----- Original Message ----
From: vince maxey vamax27@yahoo.com=20
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Me Yahoo <vamax27@yahoo.com>; pgsql-bugs@postgresql.org
Sent: Sat, November 13, 2010 3:44:03 PM
Subject: Re: [BUGS] BUG #5753: Existing Functions No Longer Work

Thanks for your response, Tom.

I guess my question would be, what needs to change in my syntax to expect t=
o get=20

one row returned?

Here are a couple of examples that do work in my existing application prior=
 to=20
my recent computer switch and re-build=A0(and I have well over 100 of these=
 types=20
of functions defined, some more complex than others, but I figured a simple=
=20
example would help someone else to most easily be able to help me).


-- Function: dimension.get_location_holiday(bigint)
-- DROP FUNCTION dimension.get_location_holiday(bigint);
CREATE OR REPLACE FUNCTION dimension.get_location_holiday(bigint)
=A0 RETURNS refcursor AS
$BODY$=20
DECLARE
=A0
=A0loc refcursor;
BEGIN
=A0open loc for=20
=A0=A0select * from dimension.location_holiday where holidayid =3D $1;=A0
=A0return loc;
END;
$BODY$
=A0 LANGUAGE plpgsql VOLATILE
=A0 COST 100;
ALTER FUNCTION dimension.get_location_holiday(bigint) OWNER TO postgres;
GRANT EXECUTE ON FUNCTION dimension.get_location_holiday(bigint) TO public;
GRANT EXECUTE ON FUNCTION dimension.get_location_holiday(bigint) TO postgre=
s;


-- Function: dimension.get_location_list(character varying, character varyi=
ng,=20
integer)
-- DROP FUNCTION dimension.get_location_list(character varying, character=
=20
varying, integer);
CREATE OR REPLACE FUNCTION dimension.get_location_list(character varying,=
=20
character varying, integer)
=A0 RETURNS refcursor AS
$BODY$=20
DECLARE
=A0loc refcursor;
BEGIN
=A0IF $3 =3D 1 THEN
=A0=A0open loc for =A0=A0
=A0=A0select a.locationid, a.locationname, a.partnerid, b.partnername,=20
a.phone1,a.phone2,=A0=A0=20

=A0=A0a.fax1, a.fax2,a.physaddress1, a.physaddress2, a.physcityid, e.city||=
',=20
'||e.statecode||'=A0 '||e.zipcode,
=A0=A0a.contact1, a.contact2, a.email1, a.email2, a.activestatus, a.timezon=
e,=20
a.taxrate, e.statecode,a.faxflag,
=A0=A0a.ticklerflag,case when a.ticklerflag =3D 't' then 'YES' else 'NO' en=
d, e.city
=A0=A0from dimension.location_base a, dimension.partner b, postal.us_zip e=
=20
=A0=A0where a.partnerid =3D b.partnerid and a.physcityid =3D e.zipid and e.=
statecode =3D=20
$2 order by a.locationname;
=A0ELSE
=A0=A0IF $3 =3D 0 THEN
=A0=A0=A0open loc for =A0=A0
=A0=A0=A0select a.locationid, a.locationname, a.partnerid, b.partnername,=
=20
a.phone1,a.phone2,=A0=A0=20

=A0=A0=A0a.fax1, a.fax2,a.physaddress1, a.physaddress2, a.physcityid, e.cit=
y||',=20
'||e.statecode||'=A0 '||e.zipcode,
=A0=A0=A0a.contact1, a.contact2, a.email1, a.email2, a.activestatus, a.time=
zone,=20
a.taxrate, e.statecode,a.faxflag,
=A0=A0=A0a.ticklerflag,case when a.ticklerflag =3D 't' then 'YES' else 'NO'=
 end, e.city
=A0=A0=A0from dimension.location_base a, dimension.partner b, postal.us_zip=
 e=20
=A0=A0=A0where a.partnerid =3D b.partnerid and a.physcityid =3D e.zipid and=
 e.statecode =3D=20
$2=20

=A0=A0=A0and lower(a.locationname) like $1||'%' order by a.locationname;=A0
=A0=A0ELSE
=A0=A0=A0open loc for =A0=A0
=A0=A0=A0select a.locationid, a.locationname, a.partnerid, b.partnername,=
=20
a.phone1,a.phone2,=A0=A0=20

=A0=A0=A0a.fax1, a.fax2,a.physaddress1, a.physaddress2, a.physcityid, e.cit=
y||',=20
'||e.statecode||'=A0 '||e.zipcode,
=A0=A0=A0a.contact1, a.contact2, a.email1, a.email2, a.activestatus, a.time=
zone,=20
a.taxrate, e.statecode,a.faxflag,
=A0=A0=A0a.ticklerflag,case when a.ticklerflag =3D 't' then 'YES' else 'NO'=
 end, e.city
=A0=A0=A0from dimension.location_base a, dimension.partner b, postal.us_zip=
 e=20
=A0=A0=A0where a.partnerid =3D b.partnerid and a.physcityid =3D e.zipid and=
 a.partnerid =3D=20
$1;
=A0
=A0=A0END IF;
=A0END IF;
=A0return loc;
END;
$BODY$
=A0 LANGUAGE plpgsql VOLATILE
=A0 COST 100;
ALTER FUNCTION dimension.get_location_list(character varying, character var=
ying,=20

integer) OWNER TO postgres;
GRANT EXECUTE ON FUNCTION dimension.get_location_list(character varying,=20
character varying, integer) TO public;
GRANT EXECUTE ON FUNCTION dimension.get_location_list(character varying,=20
character varying, integer) TO postgres;
GRANT EXECUTE ON FUNCTION dimension.get_location_list(character varying,=20
character varying, integer) TO "eMenuAdmin";


I am running my test procs from the pgAdminIII GUI.=A0 Is my syntax wrong t=
o=20
execute the function?=A0 If I run select test_proc1(3), I do get the correc=
t=20
result which is a column header (test_proc1 integer) and a value (2).=A0 So=
 why=20
wouldn't I get a 6-column result set when running select test_proc(2) ?

My java code=A0syntax is as follows:

=A0public Collection getLocationList(String pname, String ste, int type) {=
=A0=A0=A0
=A0=A0PartnerDAO ef =3D new PartnerDAO();
=A0=A0CallableStatement proc =3D null;
=A0=A0Connection conn =3D ef.getConnection();
=A0=A0Collection locations =3D new ArrayList();=A0=A0=A0=A0=A0=A0
=A0=A0try {=A0=A0=A0
=A0=A0=A0proc =3D conn.prepareCall("{ ?=3D call dimension.get_location_list=
(?,?,?) }");
=A0=A0=A0proc.registerOutParameter(1, Types.OTHER);
=A0=A0=A0=A0=A0 proc.setString(2, pname.toLowerCase().trim());
=A0=A0=A0=A0=A0 proc.setString(3, ste);
=A0=A0=A0=A0=A0 proc.setInt(4, type);=A0=A0=20
=A0=A0=A0=A0=A0 conn.setAutoCommit(false);
=A0=A0=A0=A0=A0 proc.execute();=A0=A0=A0=A0 =A0
=A0=A0=A0=A0=A0 ResultSet rs =3D (ResultSet) proc.getObject(1);
=A0=A0=A0=A0=A0 while (rs.next()) {
=A0=A0=A0=A0=A0 =A0LocationVO eRec =3D new LocationVO();=A0=A0=A0=A0=A0 =A0=
=A0=A0=A0=A0=A0 =A0
=A0=A0=A0=A0=A0 =A0eRec.setLocationId(rs.getInt(1));
=A0=A0=A0=A0=A0 =A0eRec.setLocationName(rs.getString(2));
=A0=A0=A0=A0=A0 =A0eRec.setPartnerId(rs.getInt(3));
=A0=A0=A0=A0eRec.setPartnerName(rs.getString(4));
=A0=A0=A0=A0eRec.setPhone1(rs.getString(5));
=A0=A0=A0=A0eRec.setDbphone1(rs.getString(5));
=A0=A0=A0=A0eRec.setPhone2(rs.getString(6));
=A0=A0=A0=A0eRec.setDbphone2(rs.getString(6));
=A0=A0=A0=A0eRec.setFax1(rs.getString(7));
=A0=A0=A0=A0eRec.setDbfax1(rs.getString(7));
=A0=A0=A0=A0eRec.setFax2(rs.getString(8));
=A0=A0=A0=A0eRec.setDbfax2(rs.getString(8));
=A0=A0=A0=A0eRec.setAddress1(rs.getString(9));
=A0=A0=A0=A0eRec.setAddress2(rs.getString(10));
=A0=A0=A0=A0eRec.setCityId(rs.getInt(11));
=A0=A0=A0=A0eRec.setCityName(rs.getString(12));
=A0=A0=A0=A0eRec.setContact1(rs.getString(13));
=A0=A0=A0=A0eRec.setDbcontact1(rs.getString(13));
=A0=A0=A0=A0eRec.setContact2(rs.getString(14));
=A0=A0=A0=A0eRec.setDbcontact2(rs.getString(14));
=A0=A0=A0=A0eRec.setEmail1(rs.getString(15));
=A0=A0=A0=A0eRec.setDbemail1(rs.getString(15));
=A0=A0=A0=A0eRec.setEmail2(rs.getString(16));
=A0=A0=A0=A0eRec.setDbemail2(rs.getString(16));
=A0=A0=A0=A0eRec.setStatus(rs.getInt(17));
=A0=A0=A0=A0eRec.setDbstatus(rs.getString(17));
=A0=A0=A0=A0eRec.setTimeZone(rs.getString(18));
=A0=A0=A0=A0eRec.setTaxRate(rs.getDouble(19));
=A0=A0=A0=A0eRec.setDbtaxRate(rs.getDouble(19));
=A0=A0=A0=A0eRec.setStateCode(rs.getString(20));
=A0=A0=A0=A0eRec.setFaxFlag(Boolean.parseBoolean(rs.getString(21)));
=A0=A0=A0=A0eRec.setDbfaxFlag(Boolean.parseBoolean(rs.getString(21)));
=A0=A0=A0=A0eRec.setTicklerFlag(Boolean.parseBoolean(rs.getString(22)));
=A0=A0=A0=A0eRec.setTicklerFlagText(rs.getString(23));
=A0=A0=A0=A0eRec.setScName(rs.getString(24));
=A0=A0=A0=A0eRec.setCopyMenuSourceId(0);
=A0=A0=A0=A0=A0 =A0locations.add(eRec);=A0=A0=A0=A0
=A0=A0=A0=A0=A0 }=A0=A0=A0
=A0=A0
=A0=A0} catch (Exception e) {
=A0=A0=A0e.printStackTrace();
=A0=A0}finally {
=A0=A0=A0clearResources(conn, proc);
=A0=A0}
=A0=A0return locations;=A0=A0
=A0}

If I am not including something specific required to actually display a res=
ult=20
set, can you enlighten me?=A0 How would you write a function to return a ro=
w from=20
the test data I provided?=A0 I'm stumped.

Sincerely,

Vince Maxey



----- Original Message ----
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Vince Maxey <vamax27@yahoo.com>
Cc: pgsql-bugs@postgresql.org
Sent: Sat, November 13, 2010 1:03:46 PM
Subject: Re: [BUGS] BUG #5753: Existing Functions No Longer Work

"Vince Maxey" <vamax27@yahoo.com> writes:
> Recently I upgraded a personal application built a number of years ago,
> including java, eclipse, struts and postgresql and now face an issue with
> postgresql in that application functions no longer work, specfically as
> related to refcursors.=A0 The original application was based on postgresq=
l 8.4
> I believe.
> ...
> But when I try to call the function: select test_proc(2); I get a column
> header: test_proc refcursor and the value in this column is simply: <unna=
med
> portal n>, where n seems to indicate how many times I have run a cursor f=
rom
> the SQL window.

The example you give acts exactly as I would expect, ie, it returns the
generated name of a cursor.=A0 And it does so in every release back to at
least 8.0, not just 9.0.=A0 So I think you've simplified your example to
the point that it no longer demonstrates whatever problem you're
actually having.

=A0=A0=A0 =A0=A0=A0 =A0=A0=A0 regards, tom lane

vince maxey <vamax27@yahoo.com> writes:
> My functions all worked prior to switching to 9.0 and I can excute functions
> from the pgAdminIII UI which return an individual result, such as an integer,
> but cannot test/troubleshoot those which use refcursors in the same manner; and
> they are not working within my application.  They used to work.

That's basically impossible to believe, because the behavior on this
point didn't change.  There are certainly some incompatibilities between
9.0 and previous releases, but nothing about refcursor-returning
functions specifically; and AFAICT the example functions you provided
work just the same in 9.0 as before.  So I think you're barking up the
wrong tree and the issue is something else than you think.

Perhaps you could put together a complete test case (not just the
function, but including its call) that works in 8.4 and not in 9.0?

BTW, if the gripe is specifically about what happens in the pgAdmin UI,
another possible explanation is that pgAdmin changed.  Can you reproduce
a change of behavior using just psql?

            regards, tom lane

hello

2010/11/16 vince maxey <vamax27@yahoo.com>:
> Tom, or anyone else working with this dB, can you respond to my question:
>
> How should the syntax for a function be formulated to return a refcursor
> containing one or more records?
>

http://www.network-theory.co.uk/docs/postgresql/vol2/ReturningCursors.html

I check this sample for more values

postgres=3D# select * from test;
 col
-----
 123
 333
(2 rows)

postgres=3D# begin;
BEGIN
postgres=3D# select reffunc('cursorname');
  reffunc
------------
 cursorname
(1 row)

postgres=3D# fetch all in cursorname;
 col
-----
 123
 333
(2 rows)

postgres=3D# commit;
COMMIT

Regards

Pavel Stehule


>
> I have many years SQL development experience and work with Oracle in my c=
urrent
> position.=C2=A0 I'm not a novice programmer.
>
> My functions all worked prior to switching to 9.0 and I can excute functi=
ons
> from the pgAdminIII UI which return an individual result, such as an inte=
ger,
> but cannot test/troubleshoot those which use refcursors in the same manne=
r; and
> they are not working within my application.=C2=A0 They used to work.
>
> I've provided test data and functions for your inspection and validation.=
=C2=A0 Even
> pointing me to some substantial documentation (white paper or actual book=
) that
> contains bonafide examples of how to write postgresql functions would pro=
bably
> help.=C2=A0 But simply providing syntax segments is not working,=C2=A0 I'=
ve not come
> across any examples that I can translate or compare with my existing effo=
rts.
>
> According to your documentation, new releases should be backward compatib=
le;
> other than for specific elements.=C2=A0 I would think this particular fun=
ctionality
> should be backward compatible but as I'm finding it not to be, please tak=
e some
> time to investigate and validate for yourselves what I have communicated.
>
> I really do think postgreSQL is a great database from a development
> perspective.=C2=A0 If I can get over this issue, perhaps I can provide so=
me
> documentation which others can use to create their own functions.
>
> thank you for your assistance.
>
>
> ----- Original Message ----
> From: vince maxey vamax27@yahoo.com
> To: Tom Lane <tgl@sss.pgh.pa.us>
> Cc: Me Yahoo <vamax27@yahoo.com>; pgsql-bugs@postgresql.org
> Sent: Sat, November 13, 2010 3:44:03 PM
> Subject: Re: [BUGS] BUG #5753: Existing Functions No Longer Work
>
> Thanks for your response, Tom.
>
> I guess my question would be, what needs to change in my syntax to expect=
 to get
>
> one row returned?
>
> Here are a couple of examples that do work in my existing application pri=
or to
> my recent computer switch and re-build=C2=A0(and I have well over 100 of =
these types
> of functions defined, some more complex than others, but I figured a simp=
le
> example would help someone else to most easily be able to help me).
>
>
> -- Function: dimension.get_location_holiday(bigint)
> -- DROP FUNCTION dimension.get_location_holiday(bigint);
> CREATE OR REPLACE FUNCTION dimension.get_location_holiday(bigint)
> =C2=A0 RETURNS refcursor AS
> $BODY$
> DECLARE
>
> =C2=A0loc refcursor;
> BEGIN
> =C2=A0open loc for
> =C2=A0=C2=A0select * from dimension.location_holiday where holidayid =3D =
$1;
> =C2=A0return loc;
> END;
> $BODY$
> =C2=A0 LANGUAGE plpgsql VOLATILE
> =C2=A0 COST 100;
> ALTER FUNCTION dimension.get_location_holiday(bigint) OWNER TO postgres;
> GRANT EXECUTE ON FUNCTION dimension.get_location_holiday(bigint) TO publi=
c;
> GRANT EXECUTE ON FUNCTION dimension.get_location_holiday(bigint) TO postg=
res;
>
>
> -- Function: dimension.get_location_list(character varying, character var=
ying,
> integer)
> -- DROP FUNCTION dimension.get_location_list(character varying, character
> varying, integer);
> CREATE OR REPLACE FUNCTION dimension.get_location_list(character varying,
> character varying, integer)
> =C2=A0 RETURNS refcursor AS
> $BODY$
> DECLARE
> =C2=A0loc refcursor;
> BEGIN
> =C2=A0IF $3 =3D 1 THEN
> =C2=A0=C2=A0open loc for
> =C2=A0=C2=A0select a.locationid, a.locationname, a.partnerid, b.partnerna=
me,
> a.phone1,a.phone2,
>
> =C2=A0=C2=A0a.fax1, a.fax2,a.physaddress1, a.physaddress2, a.physcityid, =
e.city||',
> '||e.statecode||'=C2=A0 '||e.zipcode,
> =C2=A0=C2=A0a.contact1, a.contact2, a.email1, a.email2, a.activestatus, a=
.timezone,
> a.taxrate, e.statecode,a.faxflag,
> =C2=A0=C2=A0a.ticklerflag,case when a.ticklerflag =3D 't' then 'YES' else=
 'NO' end, e.city
> =C2=A0=C2=A0from dimension.location_base a, dimension.partner b, postal.u=
s_zip e
> =C2=A0=C2=A0where a.partnerid =3D b.partnerid and a.physcityid =3D e.zipi=
d and e.statecode =3D
> $2 order by a.locationname;
> =C2=A0ELSE
> =C2=A0=C2=A0IF $3 =3D 0 THEN
> =C2=A0=C2=A0=C2=A0open loc for
> =C2=A0=C2=A0=C2=A0select a.locationid, a.locationname, a.partnerid, b.par=
tnername,
> a.phone1,a.phone2,
>
> =C2=A0=C2=A0=C2=A0a.fax1, a.fax2,a.physaddress1, a.physaddress2, a.physci=
tyid, e.city||',
> '||e.statecode||'=C2=A0 '||e.zipcode,
> =C2=A0=C2=A0=C2=A0a.contact1, a.contact2, a.email1, a.email2, a.activesta=
tus, a.timezone,
> a.taxrate, e.statecode,a.faxflag,
> =C2=A0=C2=A0=C2=A0a.ticklerflag,case when a.ticklerflag =3D 't' then 'YES=
' else 'NO' end, e.city
> =C2=A0=C2=A0=C2=A0from dimension.location_base a, dimension.partner b, po=
stal.us_zip e
> =C2=A0=C2=A0=C2=A0where a.partnerid =3D b.partnerid and a.physcityid =3D =
e.zipid and e.statecode =3D
> $2
>
> =C2=A0=C2=A0=C2=A0and lower(a.locationname) like $1||'%' order by a.locat=
ionname;
> =C2=A0=C2=A0ELSE
> =C2=A0=C2=A0=C2=A0open loc for
> =C2=A0=C2=A0=C2=A0select a.locationid, a.locationname, a.partnerid, b.par=
tnername,
> a.phone1,a.phone2,
>
> =C2=A0=C2=A0=C2=A0a.fax1, a.fax2,a.physaddress1, a.physaddress2, a.physci=
tyid, e.city||',
> '||e.statecode||'=C2=A0 '||e.zipcode,
> =C2=A0=C2=A0=C2=A0a.contact1, a.contact2, a.email1, a.email2, a.activesta=
tus, a.timezone,
> a.taxrate, e.statecode,a.faxflag,
> =C2=A0=C2=A0=C2=A0a.ticklerflag,case when a.ticklerflag =3D 't' then 'YES=
' else 'NO' end, e.city
> =C2=A0=C2=A0=C2=A0from dimension.location_base a, dimension.partner b, po=
stal.us_zip e
> =C2=A0=C2=A0=C2=A0where a.partnerid =3D b.partnerid and a.physcityid =3D =
e.zipid and a.partnerid =3D
> $1;
>
> =C2=A0=C2=A0END IF;
> =C2=A0END IF;
> =C2=A0return loc;
> END;
> $BODY$
> =C2=A0 LANGUAGE plpgsql VOLATILE
> =C2=A0 COST 100;
> ALTER FUNCTION dimension.get_location_list(character varying, character v=
arying,
>
> integer) OWNER TO postgres;
> GRANT EXECUTE ON FUNCTION dimension.get_location_list(character varying,
> character varying, integer) TO public;
> GRANT EXECUTE ON FUNCTION dimension.get_location_list(character varying,
> character varying, integer) TO postgres;
> GRANT EXECUTE ON FUNCTION dimension.get_location_list(character varying,
> character varying, integer) TO "eMenuAdmin";
>
>
> I am running my test procs from the pgAdminIII GUI.=C2=A0 Is my syntax wr=
ong to
> execute the function?=C2=A0 If I run select test_proc1(3), I do get the c=
orrect
> result which is a column header (test_proc1 integer) and a value (2).=C2=
=A0 So why
> wouldn't I get a 6-column result set when running select test_proc(2) ?
>
> My java code=C2=A0syntax is as follows:
>
> =C2=A0public Collection getLocationList(String pname, String ste, int typ=
e) {
> =C2=A0=C2=A0PartnerDAO ef =3D new PartnerDAO();
> =C2=A0=C2=A0CallableStatement proc =3D null;
> =C2=A0=C2=A0Connection conn =3D ef.getConnection();
> =C2=A0=C2=A0Collection locations =3D new ArrayList();
> =C2=A0=C2=A0try {
> =C2=A0=C2=A0=C2=A0proc =3D conn.prepareCall("{ ?=3D call dimension.get_lo=
cation_list(?,?,?) }");
> =C2=A0=C2=A0=C2=A0proc.registerOutParameter(1, Types.OTHER);
> =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 proc.setString(2, pname.toLowerCase().trim=
());
> =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 proc.setString(3, ste);
> =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 proc.setInt(4, type);
> =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 conn.setAutoCommit(false);
> =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 proc.execute();
> =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 ResultSet rs =3D (ResultSet) proc.getObjec=
t(1);
> =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 while (rs.next()) {
> =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 =C2=A0LocationVO eRec =3D new LocationVO();
> =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 =C2=A0eRec.setLocationId(rs.getInt(1));
> =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 =C2=A0eRec.setLocationName(rs.getString(2)=
);
> =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 =C2=A0eRec.setPartnerId(rs.getInt(3));
> =C2=A0=C2=A0=C2=A0=C2=A0eRec.setPartnerName(rs.getString(4));
> =C2=A0=C2=A0=C2=A0=C2=A0eRec.setPhone1(rs.getString(5));
> =C2=A0=C2=A0=C2=A0=C2=A0eRec.setDbphone1(rs.getString(5));
> =C2=A0=C2=A0=C2=A0=C2=A0eRec.setPhone2(rs.getString(6));
> =C2=A0=C2=A0=C2=A0=C2=A0eRec.setDbphone2(rs.getString(6));
> =C2=A0=C2=A0=C2=A0=C2=A0eRec.setFax1(rs.getString(7));
> =C2=A0=C2=A0=C2=A0=C2=A0eRec.setDbfax1(rs.getString(7));
> =C2=A0=C2=A0=C2=A0=C2=A0eRec.setFax2(rs.getString(8));
> =C2=A0=C2=A0=C2=A0=C2=A0eRec.setDbfax2(rs.getString(8));
> =C2=A0=C2=A0=C2=A0=C2=A0eRec.setAddress1(rs.getString(9));
> =C2=A0=C2=A0=C2=A0=C2=A0eRec.setAddress2(rs.getString(10));
> =C2=A0=C2=A0=C2=A0=C2=A0eRec.setCityId(rs.getInt(11));
> =C2=A0=C2=A0=C2=A0=C2=A0eRec.setCityName(rs.getString(12));
> =C2=A0=C2=A0=C2=A0=C2=A0eRec.setContact1(rs.getString(13));
> =C2=A0=C2=A0=C2=A0=C2=A0eRec.setDbcontact1(rs.getString(13));
> =C2=A0=C2=A0=C2=A0=C2=A0eRec.setContact2(rs.getString(14));
> =C2=A0=C2=A0=C2=A0=C2=A0eRec.setDbcontact2(rs.getString(14));
> =C2=A0=C2=A0=C2=A0=C2=A0eRec.setEmail1(rs.getString(15));
> =C2=A0=C2=A0=C2=A0=C2=A0eRec.setDbemail1(rs.getString(15));
> =C2=A0=C2=A0=C2=A0=C2=A0eRec.setEmail2(rs.getString(16));
> =C2=A0=C2=A0=C2=A0=C2=A0eRec.setDbemail2(rs.getString(16));
> =C2=A0=C2=A0=C2=A0=C2=A0eRec.setStatus(rs.getInt(17));
> =C2=A0=C2=A0=C2=A0=C2=A0eRec.setDbstatus(rs.getString(17));
> =C2=A0=C2=A0=C2=A0=C2=A0eRec.setTimeZone(rs.getString(18));
> =C2=A0=C2=A0=C2=A0=C2=A0eRec.setTaxRate(rs.getDouble(19));
> =C2=A0=C2=A0=C2=A0=C2=A0eRec.setDbtaxRate(rs.getDouble(19));
> =C2=A0=C2=A0=C2=A0=C2=A0eRec.setStateCode(rs.getString(20));
> =C2=A0=C2=A0=C2=A0=C2=A0eRec.setFaxFlag(Boolean.parseBoolean(rs.getString=
(21)));
> =C2=A0=C2=A0=C2=A0=C2=A0eRec.setDbfaxFlag(Boolean.parseBoolean(rs.getStri=
ng(21)));
> =C2=A0=C2=A0=C2=A0=C2=A0eRec.setTicklerFlag(Boolean.parseBoolean(rs.getSt=
ring(22)));
> =C2=A0=C2=A0=C2=A0=C2=A0eRec.setTicklerFlagText(rs.getString(23));
> =C2=A0=C2=A0=C2=A0=C2=A0eRec.setScName(rs.getString(24));
> =C2=A0=C2=A0=C2=A0=C2=A0eRec.setCopyMenuSourceId(0);
> =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 =C2=A0locations.add(eRec);
> =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 }
>
> =C2=A0=C2=A0} catch (Exception e) {
> =C2=A0=C2=A0=C2=A0e.printStackTrace();
> =C2=A0=C2=A0}finally {
> =C2=A0=C2=A0=C2=A0clearResources(conn, proc);
> =C2=A0=C2=A0}
> =C2=A0=C2=A0return locations;
> =C2=A0}
>
> If I am not including something specific required to actually display a r=
esult
> set, can you enlighten me?=C2=A0 How would you write a function to return=
 a row from
> the test data I provided?=C2=A0 I'm stumped.
>
> Sincerely,
>
> Vince Maxey
>
>
>
> ----- Original Message ----
> From: Tom Lane <tgl@sss.pgh.pa.us>
> To: Vince Maxey <vamax27@yahoo.com>
> Cc: pgsql-bugs@postgresql.org
> Sent: Sat, November 13, 2010 1:03:46 PM
> Subject: Re: [BUGS] BUG #5753: Existing Functions No Longer Work
>
> "Vince Maxey" <vamax27@yahoo.com> writes:
>> Recently I upgraded a personal application built a number of years ago,
>> including java, eclipse, struts and postgresql and now face an issue with
>> postgresql in that application functions no longer work, specfically as
>> related to refcursors.=C2=A0 The original application was based on postg=
resql 8.4
>> I believe.
>> ...
>> But when I try to call the function: select test_proc(2); I get a column
>> header: test_proc refcursor and the value in this column is simply: <unn=
amed
>> portal n>, where n seems to indicate how many times I have run a cursor =
from
>> the SQL window.
>
> The example you give acts exactly as I would expect, ie, it returns the
> generated name of a cursor.=C2=A0 And it does so in every release back to=
 at
> least 8.0, not just 9.0.=C2=A0 So I think you've simplified your example =
to
> the point that it no longer demonstrates whatever problem you're
> actually having.
>
> =C2=A0=C2=A0=C2=A0 =C2=A0=C2=A0=C2=A0 =C2=A0=C2=A0=C2=A0 regards, tom lane
>
> --
> Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-bugs
>

2010/11/16 vince maxey <vamax27@yahoo.com>:
> Here is my website:=C2=A0 www.emenusonline.net=C2=A0 It appears that the =
pg=C2=A0version my web
> host company uses is 8.1.3
>
> Right now=C2=A0this site is=C2=A0in beta mode, so you can register and or=
der food
> and=C2=A0there will be no repercussions, other than you will receive an e=
mail
> indicating you did so.=C2=A0 (No money will be collected, cc information =
or anything
> - use fake everything other than email address)
>
> If you were to register on this site and then log in, the following scree=
n shot
> is of the function that would be called to determine whether you are an e=
xisting
> valid user or not.=C2=A0 This site exists for real, gentlemen and it work=
s; or after
> you register you will never be able to log back in to the site.
>
> I reviewed the documenation referenced by Pavel and if I'm understanding =
it,
> this is only to invoke a cursor from the pgAdmin GUI, correct?=C2=A0 So i=
nstead of
> entering ' select get_user_login_info('vamax27') '=C2=A0 I should type:=
=C2=A0 =C2=A0FETCH ALL
> IN "<unnamed cursor 1>";
>
> If that is the case then why is it that each time I type select
> get_user_login_info('vamax27') the result increments by one:=C2=A0 <unnam=
ed portal 1>
> ,=C2=A0 <unnamed portal 2>,=C2=A0 <unnamed portal 3>=C2=A0 etc?
>
> portal=C2=A0 NOT=C2=A0 cursor
>
> I have lots of these functions written using the identical syntax in rega=
rd to
> refcursors and they all work on this site.=C2=A0=C2=A0I know my data is s=
et up properly
> and I know my code is good.
>
> Screenshot2 is the results of my running select refcursor_function=C2=A0 =
and
> screenshot3 is the results of my running fetch all in <unnamed portal 1> =
and
> finally screenshot4 is my trying to run fetch all in
> get_user_login_info('vamax27').=C2=A0 These are all being run from phppgA=
dmin on my
> web host provider's site.
>
> With all due respect, guys, what you are telling me doesn't add up.

you cannot to fetch data from function that returns a refcursor.

refcursor is varchar - name of some object (cursor). when you open
cursor, then you can specify a name (it's refcursor), but when you
fetch data, you must to use a direct sql identifier of cursor - not a
reference.

simply, you can't to write

fetch all from function(parameter);

Pavel


>
> It would take you minutes to set up the test data I provided and run the
> test=C2=A0function I submitted and either prove or disprove what I am say=
ing; or
> re-write the syntax so that it does return results and show me how I'm a =
fool
> and wasting your time; at which point I will apologize profusely :)
>
> I'm not trying to give you a hard time or make you do work I should be ab=
le to
> do for myself.=C2=A0 I'm out of ideas, Tom.
>
> Sincerely,
>
> Vince Maxey
>
>
>
> ----- Original Message ----
> From: Tom Lane <tgl@sss.pgh.pa.us>
> To: vince maxey <vamax27@yahoo.com>
> Cc: pgsql-bugs@postgresql.org
> Sent: Tue, November 16, 2010 11:31:28 AM
> Subject: Re: [BUGS] BUG #5753: Existing Functions No Longer Work
>
> vince maxey <vamax27@yahoo.com> writes:
>> My functions all worked prior to switching to 9.0 and I can excute funct=
ions
>> from the pgAdminIII UI which return an individual result, such as an int=
eger,
>> but cannot test/troubleshoot those which use refcursors in the same mann=
er; and
>>
>> they are not working within my application.=C2=A0 They used to work.
>
> That's basically impossible to believe, because the behavior on this
> point didn't change.=C2=A0 There are certainly some incompatibilities bet=
ween
> 9.0 and previous releases, but nothing about refcursor-returning
> functions specifically; and AFAICT the example functions you provided
> work just the same in 9.0 as before.=C2=A0 So I think you're barking up t=
he
> wrong tree and the issue is something else than you think.
>
> Perhaps you could put together a complete test case (not just the
> function, but including its call) that works in 8.4 and not in 9.0?
>
> BTW, if the gripe is specifically about what happens in the pgAdmin UI,
> another possible explanation is that pgAdmin changed.=C2=A0 Can you repro=
duce
> a change of behavior using just psql?
>
> =C2=A0=C2=A0=C2=A0 =C2=A0=C2=A0=C2=A0 =C2=A0=C2=A0=C2=A0 regards, tom lane
>

/*

you cannot to fetch data from function that returns a refcursor.

refcursor is varchar - name of some object (cursor). when you open
cursor, then you can specify a name (it's refcursor), but when you
fetch data, you must to use a direct sql identifier of cursor - not a
reference.

simply, you can't to write

fetch all from function(parameter);

*/

Please explain to me then why my application works.=A0=20

You are speaking to me entirely in abstract, where I have provided you with=
=20
several concrete examples of functions that work when called from the java=
=20
application.=A0 I also provided you evidence that using the terms in your=
=20
documentation do not work.=A0 What am I doing wrong?

Again, I'm imploring you both:=A0 Use the data I provided to create a test =
table.=A0=20
Create the function I have created (test_proc and test_proc1) and run it or=
=20
re-write it so that it works and please provide that back to me along with =
the=20
syntax I should use to make it work.

Please, prove your documentation applies to=A0my data because as it stands =
from my=20
perspective your documentation is fairly poor in explaining how to write an=
d=20
call functions, considering they are a fairly common tool for developers an=
d=20
DBAs.=A0 Why can I not Google for postgreSQL function examples?=A0 I submit=
 it=A0might=20
be=A0for this reason: perhaps people stay away from them because they have =
flaws,=20
either in how they have been communicated or in how they are invoked....

I've worked extensively with SQL Server, IBM, Sybase and Oracle.=A0 I know =
how to=20
write and test stored procedures.=A0=20


I'm telling you that something is different between 8.1 and 9.0 and all you=
 are=20
offering are conceptual excerpts from your own documentation.=A0=20


Please put it back to me using my data and you might get some insight into =
what=20
could be tweaked with your documentation so that it can be more intuitive a=
nd=20
functions used more often....

=A0


----- Original Message ----
From: Pavel Stehule <pavel.stehule@gmail.com>
To: vince maxey <vamax27@yahoo.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-bugs@postgresql.org
Sent: Tue, November 16, 2010 1:14:14 PM
Subject: Re: [BUGS] BUG #5753: Existing Functions No Longer Work

2010/11/16 vince maxey <vamax27@yahoo.com>:
> Here is my website:=A0 www.emenusonline.net=A0 It appears that the pg=A0v=
ersion my=20
>web
> host company uses is 8.1.3
>
> Right now=A0this site is=A0in beta mode, so you can register and order fo=
od
> and=A0there will be no repercussions, other than you will receive an email
> indicating you did so.=A0 (No money will be collected, cc information or=
=20
anything
> - use fake everything other than email address)
>
> If you were to register on this site and then log in, the following scree=
n=20
shot
> is of the function that would be called to determine whether you are an=
=20
>existing
> valid user or not.=A0 This site exists for real, gentlemen and it works; =
or=20
after
> you register you will never be able to log back in to the site.
>
> I reviewed the documenation referenced by Pavel and if I'm understanding =
it,
> this is only to invoke a cursor from the pgAdmin GUI, correct?=A0 So inst=
ead of
> entering ' select get_user_login_info('vamax27') '=A0 I should type:=A0 =
=A0FETCH ALL
> IN "<unnamed cursor 1>";
>
> If that is the case then why is it that each time I type select
> get_user_login_info('vamax27') the result increments by one:=A0 <unnamed =
portal=20
>1>
> ,=A0 <unnamed portal 2>,=A0 <unnamed portal 3>=A0 etc?
>
> portal=A0 NOT=A0 cursor
>
> I have lots of these functions written using the identical syntax in rega=
rd to
> refcursors and they all work on this site.=A0=A0I know my data is set up =
properly
> and I know my code is good.
>
> Screenshot2 is the results of my running select refcursor_function=A0 and
> screenshot3 is the results of my running fetch all in <unnamed portal 1> =
and
> finally screenshot4 is my trying to run fetch all in
> get_user_login_info('vamax27').=A0 These are all being run from phppgAdmi=
n on my
> web host provider's site.
>
> With all due respect, guys, what you are telling me doesn't add up.

you cannot to fetch data from function that returns a refcursor.

refcursor is varchar - name of some object (cursor). when you open
cursor, then you can specify a name (it's refcursor), but when you
fetch data, you must to use a direct sql identifier of cursor - not a
reference.

simply, you can't to write

fetch all from function(parameter);

Pavel


>
> It would take you minutes to set up the test data I provided and run the
> test=A0function I submitted and either prove or disprove what I am saying=
; or
> re-write the syntax so that it does return results and show me how I'm a =
fool
> and wasting your time; at which point I will apologize profusely :)
>
> I'm not trying to give you a hard time or make you do work I should be ab=
le to
> do for myself.=A0 I'm out of ideas, Tom.
>
> Sincerely,
>
> Vince Maxey
>
>
>
> ----- Original Message ----
> From: Tom Lane <tgl@sss.pgh.pa.us>
> To: vince maxey <vamax27@yahoo.com>
> Cc: pgsql-bugs@postgresql.org
> Sent: Tue, November 16, 2010 11:31:28 AM
> Subject: Re: [BUGS] BUG #5753: Existing Functions No Longer Work
>
> vince maxey <vamax27@yahoo.com> writes:
>> My functions all worked prior to switching to 9.0 and I can excute funct=
ions
>> from the pgAdminIII UI which return an individual result, such as an int=
eger,
>> but cannot test/troubleshoot those which use refcursors in the same mann=
er;=20
>and
>>
>> they are not working within my application.=A0 They used to work.
>
> That's basically impossible to believe, because the behavior on this
> point didn't change.=A0 There are certainly some incompatibilities between
> 9.0 and previous releases, but nothing about refcursor-returning
> functions specifically; and AFAICT the example functions you provided
> work just the same in 9.0 as before.=A0 So I think you're barking up the
> wrong tree and the issue is something else than you think.
>
> Perhaps you could put together a complete test case (not just the
> function, but including its call) that works in 8.4 and not in 9.0?
>
> BTW, if the gripe is specifically about what happens in the pgAdmin UI,
> another possible explanation is that pgAdmin changed.=A0 Can you reproduce
> a change of behavior using just psql?
>
> =A0=A0=A0 =A0=A0=A0 =A0=A0=A0 regards, tom lane
>

> /*
>
> you cannot to fetch data from function that returns a refcursor.
>
> refcursor is varchar - name of some object (cursor). when you open
> cursor, then you can specify a name (it's refcursor), but when you
> fetch data, you must to use a direct sql identifier of cursor - not a
> reference.
>
> simply, you can't to write
>
> fetch all from function(parameter);
>
> */
>
> Please explain to me then why my application works.
>
> You are speaking to me entirely in abstract, where I have provided
> you with
> several concrete examples of functions that work when called from
> the java
> application.  I also provided you evidence that using the terms in
> your
> documentation do not work.  What am I doing wrong?
>
> Again, I'm imploring you both:  Use the data I provided to create a
> test table.
> Create the function I have created (test_proc and test_proc1) and
> run it or
> re-write it so that it works and please provide that back to me
> along with the
> syntax I should use to make it work.
>
> Please, prove your documentation applies to my data because as it
> stands from my
> perspective your documentation is fairly poor in explaining how to
> write and
> call functions, considering they are a fairly common tool for
> developers and
> DBAs.  Why can I not Google for postgreSQL function examples?  I
> submit it might
> be for this reason: perhaps people stay away from them because they
> have flaws,
> either in how they have been communicated or in how they are
> invoked....
>
> I've worked extensively with SQL Server, IBM, Sybase and Oracle.  I
> know how to
> write and test stored procedures.
>
>
> I'm telling you that something is different between 8.1 and 9.0 and
> all you are
> offering are conceptual excerpts from your own documentation.
>
>
> Please put it back to me using my data and you might get some
> insight into what
> could be tweaked with your documentation so that it can be more
> intuitive and
> functions used more often....

Vince - is there any chance that you were using a non-standard
PostgreSQL distribution or driver before?  Like something from
EnterpriseDB?  It may be that a third-party JDBC driver was doing some
behind-the-curtains work on your behalf.

        -- Korry

-----------------------------------------------------------------------
Korry Douglas
Senior Database Dude
EnterpriseDB Corporation
The Enterprise Postgres Company

Phone: (804)241-4301
Mobile: (620) EDB-NERD

On Tue, 16 Nov 2010, Korry Douglas wrote:

> Vince - is there any chance that you were using a non-standard PostgreSQL
> distribution or driver before?  Like something from EnterpriseDB?  It may be
> that a third-party JDBC driver was doing some behind-the-curtains work on
> your behalf.
>

The stock JDBC driver does have support for refcursors, so that it will do
FETCH ALL FROM it and return a ResultSet object instead of just a string
if you call getObject on a refcursor value.  This thread is too confusing
with random snippets, assertions, and differences between pgadmin, psql,
and other interfaces to tell what's really going on.  The behavior of the
JDBC driver shouldn't have changed between releases either, but this may
explain the differences seen between pgadmin and the user's application.

http://jdbc.postgresql.org/documentation/84/callproc.html#callproc-resultset-refcursor

Kris Jurka

On 17/11/10 05:42, vince maxey wrote:

> You are speaking to me entirely in abstract, where I have provided you with
> several concrete examples of functions that work when called from the java
> application.

I've only been following this thread broadly, but I haven't seen a
complete and self-contained test case (Java example code, and SQL
database creation script) that demonstrates the issue yet. Where's the
executable .jar and source .zip ?

When I had issues with PostgreSQL and JDBC (specifically, with client
certificate support) I wrote a simple, self-contained test case that
clearly demonstrated the problem. This meant that everybody could test
it easily, could see what was going on, and was working with the same
thing. Getting from there to a fix was much easier as a result.

Consider doing the same. I'd be testing and trying to help if there was
enough information in this thread to do so without spending lots of my
time on it - time I'm not working for pay, or doing something more fun.

> I'm telling you that something is different between 8.1 and 9.0 and all you are
> offering are conceptual excerpts from your own documentation.

You've offered only incomplete views of part of what you're doing too,
so I'd hesitate to push this too hard onto the people who've been
spending their time trying to help you.

Your original post doesn't provide the code that's executing the
queries, and your follow-up would still require a bunch of work to turn
into an executable test case. Tom in particular doesn't do all that much
with Java, and probably won't be taking the time to write a test-case
for you.

Even now, I can't find any mention of the JDBC driver version you're
using in these tests.

> I've worked extensively with SQL Server, IBM, Sybase and Oracle.  I know how to
> write and test stored procedures.

Unfortunately, PostgreSQL does not really support stored procedures. It
supports stored *functions* that run within the context of a normal SQL
statement, but your function still runs within a SELECT and is
restricted in its control over transactions among other things.

The JDBC driver maps the JDBC stored procedure call syntax to call a Pg
stored function. This works rather well, though I hate to think how
it'll be handled when real stored procedures are implemented at some point.

I'm not sure this matters much in the context of your current issue, but
it's worth bearing in mind.

--
System & Network Administrator
POST Newspapers

On Tue, Nov 16, 2010 at 5:13 PM, Kris Jurka <books@ejurka.com> wrote:
> On Tue, 16 Nov 2010, Korry Douglas wrote:
>
>> Vince - is there any chance that you were using a non-standard PostgreSQL
>> distribution or driver before? =A0Like something from EnterpriseDB? =A0I=
t may be
>> that a third-party JDBC driver was doing some behind-the-curtains work on
>> your behalf.
>>
>
> The stock JDBC driver does have support for refcursors, so that it will do
> FETCH ALL FROM it and return a ResultSet object instead of just a string =
if
> you call getObject on a refcursor value. =A0This thread is too confusing =
with
> random snippets, assertions, and differences between pgadmin, psql, and
> other interfaces to tell what's really going on. =A0The behavior of the J=
DBC
> driver shouldn't have changed between releases either, but this may expla=
in
> the differences seen between pgadmin and the user's application.
>
> http://jdbc.postgresql.org/documentation/84/callproc.html#callproc-result=
set-refcursor

This seems likely to be related to the OP's problem, because his code
snippet does in fact show him calling getObject()...

--=20
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company