Re: BUG #5753: Existing Functions No Longer Work - Mailing list pgsql-bugs

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