Re: BUG #5753: Existing Functions No Longer Work

hello

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

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

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

postgres=3D# commit;
COMMIT

Regards

Pavel Stehule


>
> I have many years SQL development experience and work with Oracle in my c=
urrent
> position.=C2=A0 I'm not a novice programmer.
>
> My functions all worked prior to switching to 9.0 and I can excute functi=
ons
> from the pgAdminIII UI which return an individual result, such as an inte=
ger,
> but cannot test/troubleshoot those which use refcursors in the same manne=
r; and
> they are not working within my application.=C2=A0 They used to work.
>
> I've provided test data and functions for your inspection and validation.=
=C2=A0 Even
> pointing me to some substantial documentation (white paper or actual book=
) that
> contains bonafide examples of how to write postgresql functions would pro=
bably
> help.=C2=A0 But simply providing syntax segments is not working,=C2=A0 I'=
ve not come
> across any examples that I can translate or compare with my existing effo=
rts.
>
> According to your documentation, new releases should be backward compatib=
le;
> other than for specific elements.=C2=A0 I would think this particular fun=
ctionality
> should be backward compatible but as I'm finding it not to be, please tak=
e 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.=C2=A0 If I can get over this issue, perhaps I can provide so=
me
> documentation which others can use to create their own functions.
>
> thank you for your assistance.
>
>
> ----- Original Message ----
> From: vince maxey vamax27@yahoo.com
> To: Tom Lane <tgl@sss.pgh.pa.us>
> Cc: Me Yahoo <vamax27@yahoo.com>; pgsql-bugs@postgresql.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 pri=
or to
> my recent computer switch and re-build=C2=A0(and I have well over 100 of =
these types
> of functions defined, some more complex than others, but I figured a simp=
le
> 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)
> =C2=A0 RETURNS refcursor AS
> $BODY$
> DECLARE
>
> =C2=A0loc refcursor;
> BEGIN
> =C2=A0open loc for
> =C2=A0=C2=A0select * from dimension.location_holiday where holidayid =3D =
$1;
> =C2=A0return loc;
> END;
> $BODY$
> =C2=A0 LANGUAGE plpgsql VOLATILE
> =C2=A0 COST 100;
> ALTER FUNCTION dimension.get_location_holiday(bigint) OWNER TO postgres;
> GRANT EXECUTE ON FUNCTION dimension.get_location_holiday(bigint) TO publi=
c;
> GRANT EXECUTE ON FUNCTION dimension.get_location_holiday(bigint) TO postg=
res;
>
>
> -- Function: dimension.get_location_list(character varying, character var=
ying,
> 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)
> =C2=A0 RETURNS refcursor AS
> $BODY$
> DECLARE
> =C2=A0loc refcursor;
> BEGIN
> =C2=A0IF $3 =3D 1 THEN
> =C2=A0=C2=A0open loc for
> =C2=A0=C2=A0select a.locationid, a.locationname, a.partnerid, b.partnerna=
me,
> a.phone1,a.phone2,
>
> =C2=A0=C2=A0a.fax1, a.fax2,a.physaddress1, a.physaddress2, a.physcityid, =
e.city||',
> '||e.statecode||'=C2=A0 '||e.zipcode,
> =C2=A0=C2=A0a.contact1, a.contact2, a.email1, a.email2, a.activestatus, a=
.timezone,
> a.taxrate, e.statecode,a.faxflag,
> =C2=A0=C2=A0a.ticklerflag,case when a.ticklerflag =3D 't' then 'YES' else=
 'NO' end, e.city
> =C2=A0=C2=A0from dimension.location_base a, dimension.partner b, postal.u=
s_zip e
> =C2=A0=C2=A0where a.partnerid =3D b.partnerid and a.physcityid =3D e.zipi=
d and e.statecode =3D
> $2 order by a.locationname;
> =C2=A0ELSE
> =C2=A0=C2=A0IF $3 =3D 0 THEN
> =C2=A0=C2=A0=C2=A0open loc for
> =C2=A0=C2=A0=C2=A0select a.locationid, a.locationname, a.partnerid, b.par=
tnername,
> a.phone1,a.phone2,
>
> =C2=A0=C2=A0=C2=A0a.fax1, a.fax2,a.physaddress1, a.physaddress2, a.physci=
tyid, e.city||',
> '||e.statecode||'=C2=A0 '||e.zipcode,
> =C2=A0=C2=A0=C2=A0a.contact1, a.contact2, a.email1, a.email2, a.activesta=
tus, a.timezone,
> a.taxrate, e.statecode,a.faxflag,
> =C2=A0=C2=A0=C2=A0a.ticklerflag,case when a.ticklerflag =3D 't' then 'YES=
' else 'NO' end, e.city
> =C2=A0=C2=A0=C2=A0from dimension.location_base a, dimension.partner b, po=
stal.us_zip e
> =C2=A0=C2=A0=C2=A0where a.partnerid =3D b.partnerid and a.physcityid =3D =
e.zipid and e.statecode =3D
> $2
>
> =C2=A0=C2=A0=C2=A0and lower(a.locationname) like $1||'%' order by a.locat=
ionname;
> =C2=A0=C2=A0ELSE
> =C2=A0=C2=A0=C2=A0open loc for
> =C2=A0=C2=A0=C2=A0select a.locationid, a.locationname, a.partnerid, b.par=
tnername,
> a.phone1,a.phone2,
>
> =C2=A0=C2=A0=C2=A0a.fax1, a.fax2,a.physaddress1, a.physaddress2, a.physci=
tyid, e.city||',
> '||e.statecode||'=C2=A0 '||e.zipcode,
> =C2=A0=C2=A0=C2=A0a.contact1, a.contact2, a.email1, a.email2, a.activesta=
tus, a.timezone,
> a.taxrate, e.statecode,a.faxflag,
> =C2=A0=C2=A0=C2=A0a.ticklerflag,case when a.ticklerflag =3D 't' then 'YES=
' else 'NO' end, e.city
> =C2=A0=C2=A0=C2=A0from dimension.location_base a, dimension.partner b, po=
stal.us_zip e
> =C2=A0=C2=A0=C2=A0where a.partnerid =3D b.partnerid and a.physcityid =3D =
e.zipid and a.partnerid =3D
> $1;
>
> =C2=A0=C2=A0END IF;
> =C2=A0END IF;
> =C2=A0return loc;
> END;
> $BODY$
> =C2=A0 LANGUAGE plpgsql VOLATILE
> =C2=A0 COST 100;
> ALTER FUNCTION dimension.get_location_list(character varying, character v=
arying,
>
> 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.=C2=A0 Is my syntax wr=
ong to
> execute the function?=C2=A0 If I run select test_proc1(3), I do get the c=
orrect
> result which is a column header (test_proc1 integer) and a value (2).=C2=
=A0 So why
> wouldn't I get a 6-column result set when running select test_proc(2) ?
>
> My java code=C2=A0syntax is as follows:
>
> =C2=A0public Collection getLocationList(String pname, String ste, int typ=
e) {
> =C2=A0=C2=A0PartnerDAO ef =3D new PartnerDAO();
> =C2=A0=C2=A0CallableStatement proc =3D null;
> =C2=A0=C2=A0Connection conn =3D ef.getConnection();
> =C2=A0=C2=A0Collection locations =3D new ArrayList();
> =C2=A0=C2=A0try {
> =C2=A0=C2=A0=C2=A0proc =3D conn.prepareCall("{ ?=3D call dimension.get_lo=
cation_list(?,?,?) }");
> =C2=A0=C2=A0=C2=A0proc.registerOutParameter(1, Types.OTHER);
> =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 proc.setString(2, pname.toLowerCase().trim=
());
> =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 proc.setString(3, ste);
> =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 proc.setInt(4, type);
> =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 conn.setAutoCommit(false);
> =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 proc.execute();
> =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 ResultSet rs =3D (ResultSet) proc.getObjec=
t(1);
> =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 while (rs.next()) {
> =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 =C2=A0LocationVO eRec =3D new LocationVO();
> =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 =C2=A0eRec.setLocationId(rs.getInt(1));
> =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 =C2=A0eRec.setLocationName(rs.getString(2)=
);
> =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 =C2=A0eRec.setPartnerId(rs.getInt(3));
> =C2=A0=C2=A0=C2=A0=C2=A0eRec.setPartnerName(rs.getString(4));
> =C2=A0=C2=A0=C2=A0=C2=A0eRec.setPhone1(rs.getString(5));
> =C2=A0=C2=A0=C2=A0=C2=A0eRec.setDbphone1(rs.getString(5));
> =C2=A0=C2=A0=C2=A0=C2=A0eRec.setPhone2(rs.getString(6));
> =C2=A0=C2=A0=C2=A0=C2=A0eRec.setDbphone2(rs.getString(6));
> =C2=A0=C2=A0=C2=A0=C2=A0eRec.setFax1(rs.getString(7));
> =C2=A0=C2=A0=C2=A0=C2=A0eRec.setDbfax1(rs.getString(7));
> =C2=A0=C2=A0=C2=A0=C2=A0eRec.setFax2(rs.getString(8));
> =C2=A0=C2=A0=C2=A0=C2=A0eRec.setDbfax2(rs.getString(8));
> =C2=A0=C2=A0=C2=A0=C2=A0eRec.setAddress1(rs.getString(9));
> =C2=A0=C2=A0=C2=A0=C2=A0eRec.setAddress2(rs.getString(10));
> =C2=A0=C2=A0=C2=A0=C2=A0eRec.setCityId(rs.getInt(11));
> =C2=A0=C2=A0=C2=A0=C2=A0eRec.setCityName(rs.getString(12));
> =C2=A0=C2=A0=C2=A0=C2=A0eRec.setContact1(rs.getString(13));
> =C2=A0=C2=A0=C2=A0=C2=A0eRec.setDbcontact1(rs.getString(13));
> =C2=A0=C2=A0=C2=A0=C2=A0eRec.setContact2(rs.getString(14));
> =C2=A0=C2=A0=C2=A0=C2=A0eRec.setDbcontact2(rs.getString(14));
> =C2=A0=C2=A0=C2=A0=C2=A0eRec.setEmail1(rs.getString(15));
> =C2=A0=C2=A0=C2=A0=C2=A0eRec.setDbemail1(rs.getString(15));
> =C2=A0=C2=A0=C2=A0=C2=A0eRec.setEmail2(rs.getString(16));
> =C2=A0=C2=A0=C2=A0=C2=A0eRec.setDbemail2(rs.getString(16));
> =C2=A0=C2=A0=C2=A0=C2=A0eRec.setStatus(rs.getInt(17));
> =C2=A0=C2=A0=C2=A0=C2=A0eRec.setDbstatus(rs.getString(17));
> =C2=A0=C2=A0=C2=A0=C2=A0eRec.setTimeZone(rs.getString(18));
> =C2=A0=C2=A0=C2=A0=C2=A0eRec.setTaxRate(rs.getDouble(19));
> =C2=A0=C2=A0=C2=A0=C2=A0eRec.setDbtaxRate(rs.getDouble(19));
> =C2=A0=C2=A0=C2=A0=C2=A0eRec.setStateCode(rs.getString(20));
> =C2=A0=C2=A0=C2=A0=C2=A0eRec.setFaxFlag(Boolean.parseBoolean(rs.getString=
(21)));
> =C2=A0=C2=A0=C2=A0=C2=A0eRec.setDbfaxFlag(Boolean.parseBoolean(rs.getStri=
ng(21)));
> =C2=A0=C2=A0=C2=A0=C2=A0eRec.setTicklerFlag(Boolean.parseBoolean(rs.getSt=
ring(22)));
> =C2=A0=C2=A0=C2=A0=C2=A0eRec.setTicklerFlagText(rs.getString(23));
> =C2=A0=C2=A0=C2=A0=C2=A0eRec.setScName(rs.getString(24));
> =C2=A0=C2=A0=C2=A0=C2=A0eRec.setCopyMenuSourceId(0);
> =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 =C2=A0locations.add(eRec);
> =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 }
>
> =C2=A0=C2=A0} catch (Exception e) {
> =C2=A0=C2=A0=C2=A0e.printStackTrace();
> =C2=A0=C2=A0}finally {
> =C2=A0=C2=A0=C2=A0clearResources(conn, proc);
> =C2=A0=C2=A0}
> =C2=A0=C2=A0return locations;
> =C2=A0}
>
> If I am not including something specific required to actually display a r=
esult
> set, can you enlighten me?=C2=A0 How would you write a function to return=
 a row from
> the test data I provided?=C2=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.=C2=A0 The original application was based on postg=
resql 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: <unn=
amed
>> 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.=C2=A0 And it does so in every release back to=
 at
> least 8.0, not just 9.0.=C2=A0 So I think you've simplified your example =
to
> the point that it no longer demonstrates whatever problem you're
> actually having.
>
> =C2=A0=C2=A0=C2=A0 =C2=A0=C2=A0=C2=A0 =C2=A0=C2=A0=C2=A0 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
>