Re: 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 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 <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Vince Maxey <vamax27(at)yahoo(dot)com>
Cc: pgsql-bugs(at)postgresql(dot)org
Sent: Sat, November 13, 2010 1:03:46 PM
Subject: Re: [BUGS] BUG #5753: Existing Functions No Longer Work

"Vince Maxey" <vamax27(at)yahoo(dot)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