Re: BUG #5753: Existing Functions No Longer Work

hello

2010/11/16 vince maxey <vamax27(at)yahoo(dot)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=# select * from test;
col
-----
123
333
(2 rows)

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

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

postgres=# commit;
COMMIT

Regards

Pavel Stehule

>
> 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
>
> --
> Sent via pgsql-bugs mailing list (pgsql-bugs(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-bugs
>