Skip site navigation (1) Skip section navigation (2)

Re: BUG #5753: Existing Functions No Longer Work

From: vince maxey <vamax27(at)yahoo(dot)com>
To: tgl(at)sss(dot)pgh(dot)pa(dot)us, pgsql-bugs(at)postgresql(dot)org
Cc: Me Yahoo <vamax27(at)yahoo(dot)com>
Subject: Re: BUG #5753: Existing Functions No Longer Work
Date: 2010-11-16 17:04:59
Message-ID: 237745.519.qm@web57613.mail.re1.yahoo.com (view raw or flat)
Thread:
Lists: pgsql-bugs
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?  


I have many years SQL development experience and work with Oracle in my current 
position.  I'm not a novice programmer.

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.

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

According to your documentation, new releases should be backward compatible; 
other than for specific elements.  I would think this particular functionality 
should be backward compatible but as I'm finding it not to be, please take 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.  If I can get over this issue, perhaps I can provide some 
documentation which others can use to create their own functions.

thank you for your assistance.


----- Original Message ----
From: vince maxey vamax27(at)yahoo(dot)com 
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: Me Yahoo <vamax27(at)yahoo(dot)com>; pgsql-bugs(at)postgresql(dot)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 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

In response to

Responses

pgsql-bugs by date

Next:From: Tom LaneDate: 2010-11-16 17:31:28
Subject: Re: BUG #5753: Existing Functions No Longer Work
Previous:From: Jon NelsonDate: 2010-11-16 15:48:32
Subject: Problem with ALTER TABLE - occasional "tuple concurrently updated"

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group