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 prior to
my recent computer switch and re-build (and I have well over 100 of these types
of functions defined, some more complex than others, but I figured a simple
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)
RETURNS refcursor AS
$BODY$
DECLARE
loc refcursor;
BEGIN
open loc for
select * from dimension.location_holiday where holidayid = $1;
return loc;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
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 postgres;
-- Function: dimension.get_location_list(character varying, character varying,
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)
RETURNS refcursor AS
$BODY$
DECLARE
loc refcursor;
BEGIN
IF $3 = 1 THEN
open loc for
select a.locationid, a.locationname, a.partnerid, b.partnername,
a.phone1,a.phone2,
a.fax1, a.fax2,a.physaddress1, a.physaddress2, a.physcityid, e.city||',
'||e.statecode||' '||e.zipcode,
a.contact1, a.contact2, a.email1, a.email2, a.activestatus, a.timezone,
a.taxrate, e.statecode,a.faxflag,
a.ticklerflag,case when a.ticklerflag = 't' then 'YES' else 'NO' end, e.city
from dimension.location_base a, dimension.partner b, postal.us_zip e
where a.partnerid = b.partnerid and a.physcityid = e.zipid and e.statecode =
$2 order by a.locationname;
ELSE
IF $3 = 0 THEN
open loc for
select a.locationid, a.locationname, a.partnerid, b.partnername,
a.phone1,a.phone2,
a.fax1, a.fax2,a.physaddress1, a.physaddress2, a.physcityid, e.city||',
'||e.statecode||' '||e.zipcode,
a.contact1, a.contact2, a.email1, a.email2, a.activestatus, a.timezone,
a.taxrate, e.statecode,a.faxflag,
a.ticklerflag,case when a.ticklerflag = 't' then 'YES' else 'NO' end, e.city
from dimension.location_base a, dimension.partner b, postal.us_zip e
where a.partnerid = b.partnerid and a.physcityid = e.zipid and e.statecode =
$2
and lower(a.locationname) like $1||'%' order by a.locationname;
ELSE
open loc for
select a.locationid, a.locationname, a.partnerid, b.partnername,
a.phone1,a.phone2,
a.fax1, a.fax2,a.physaddress1, a.physaddress2, a.physcityid, e.city||',
'||e.statecode||' '||e.zipcode,
a.contact1, a.contact2, a.email1, a.email2, a.activestatus, a.timezone,
a.taxrate, e.statecode,a.faxflag,
a.ticklerflag,case when a.ticklerflag = 't' then 'YES' else 'NO' end, e.city
from dimension.location_base a, dimension.partner b, postal.us_zip e
where a.partnerid = b.partnerid and a.physcityid = e.zipid and a.partnerid =
$1;
END IF;
END IF;
return loc;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
ALTER FUNCTION dimension.get_location_list(character varying, character
varying,
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. 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) ?
My java code syntax is as follows:
public Collection getLocationList(String pname, String ste, int type) {
PartnerDAO ef = new PartnerDAO();
CallableStatement proc = null;
Connection conn = ef.getConnection();
Collection locations = new ArrayList();
try {
proc = conn.prepareCall("{ ?= call dimension.get_location_list(?,?,?) }");
proc.registerOutParameter(1, Types.OTHER);
proc.setString(2, pname.toLowerCase().trim());
proc.setString(3, ste);
proc.setInt(4, type);
conn.setAutoCommit(false);
proc.execute();
ResultSet rs = (ResultSet) proc.getObject(1);
while (rs.next()) {
LocationVO eRec = new LocationVO();
eRec.setLocationId(rs.getInt(1));
eRec.setLocationName(rs.getString(2));
eRec.setPartnerId(rs.getInt(3));
eRec.setPartnerName(rs.getString(4));
eRec.setPhone1(rs.getString(5));
eRec.setDbphone1(rs.getString(5));
eRec.setPhone2(rs.getString(6));
eRec.setDbphone2(rs.getString(6));
eRec.setFax1(rs.getString(7));
eRec.setDbfax1(rs.getString(7));
eRec.setFax2(rs.getString(8));
eRec.setDbfax2(rs.getString(8));
eRec.setAddress1(rs.getString(9));
eRec.setAddress2(rs.getString(10));
eRec.setCityId(rs.getInt(11));
eRec.setCityName(rs.getString(12));
eRec.setContact1(rs.getString(13));
eRec.setDbcontact1(rs.getString(13));
eRec.setContact2(rs.getString(14));
eRec.setDbcontact2(rs.getString(14));
eRec.setEmail1(rs.getString(15));
eRec.setDbemail1(rs.getString(15));
eRec.setEmail2(rs.getString(16));
eRec.setDbemail2(rs.getString(16));
eRec.setStatus(rs.getInt(17));
eRec.setDbstatus(rs.getString(17));
eRec.setTimeZone(rs.getString(18));
eRec.setTaxRate(rs.getDouble(19));
eRec.setDbtaxRate(rs.getDouble(19));
eRec.setStateCode(rs.getString(20));
eRec.setFaxFlag(Boolean.parseBoolean(rs.getString(21)));
eRec.setDbfaxFlag(Boolean.parseBoolean(rs.getString(21)));
eRec.setTicklerFlag(Boolean.parseBoolean(rs.getString(22)));
eRec.setTicklerFlagText(rs.getString(23));
eRec.setScName(rs.getString(24));
eRec.setCopyMenuSourceId(0);
locations.add(eRec);
}
} catch (Exception e) {
e.printStackTrace();
}finally {
clearResources(conn, proc);
}
return locations;
}
If I am not including something specific required to actually display a result
set, can you enlighten me? How would you write a function to return a row from
the test data I provided? I'm stumped.
Sincerely,
Vince Maxey
----- Original Message ----
From: Tom Lane <t...@sss.pgh.pa.us>
To: Vince Maxey <vama...@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" <vama...@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
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
