Re: BUG #5753: Existing Functions No Longer Work - Mailing list pgsql-bugs
From | vince maxey |
---|---|
Subject | Re: BUG #5753: Existing Functions No Longer Work |
Date | |
Msg-id | 237745.519.qm@web57613.mail.re1.yahoo.com Whole thread Raw |
In response to | Re: BUG #5753: Existing Functions No Longer Work (vince maxey <vamax27@yahoo.com>) |
Responses |
Re: BUG #5753: Existing Functions No Longer Work
Re: BUG #5753: Existing Functions No Longer Work |
List | 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=20 containing one or more records?=A0=20 I have many years SQL development experience and work with Oracle in my cur= rent=20 position.=A0 I'm not a novice programmer. My functions all worked prior to switching to 9.0 and I can excute function= s=20 from the pgAdminIII UI which return an individual result, such as an intege= r,=20 but cannot test/troubleshoot those which use refcursors in the same manner;= and=20 they are not working within my application.=A0 They used to work. I've provided test data and functions for your inspection and validation.= =A0 Even=20 pointing me to some substantial documentation (white paper or actual book) = that=20 contains bonafide examples of how to write postgresql functions would proba= bly=20 help.=A0 But simply providing syntax segments is not working,=A0 I've not c= ome=20 across any examples that I can translate or compare with my existing effort= s. According to your documentation, new releases should be backward compatible= ;=20 other than for specific elements.=A0 I would think this particular function= ality=20 should be backward compatible but as I'm finding it not to be, please take = some=20 time to investigate and validate for yourselves what I have communicated. I really do think postgreSQL is a great database from a development=20 perspective.=A0 If I can get over this issue, perhaps I can provide some=20 documentation which others can use to create their own functions. thank you for your assistance. ----- Original Message ---- From: vince maxey vamax27@yahoo.com=20 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 t= o get=20 one row returned? Here are a couple of examples that do work in my existing application prior= to=20 my recent computer switch and re-build=A0(and I have well over 100 of these= types=20 of functions defined, some more complex than others, but I figured a simple= =20 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) =A0 RETURNS refcursor AS $BODY$=20 DECLARE =A0 =A0loc refcursor; BEGIN =A0open loc for=20 =A0=A0select * from dimension.location_holiday where holidayid =3D $1;=A0 =A0return loc; END; $BODY$ =A0 LANGUAGE plpgsql VOLATILE =A0 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 postgre= s; -- Function: dimension.get_location_list(character varying, character varyi= ng,=20 integer) -- DROP FUNCTION dimension.get_location_list(character varying, character= =20 varying, integer); CREATE OR REPLACE FUNCTION dimension.get_location_list(character varying,= =20 character varying, integer) =A0 RETURNS refcursor AS $BODY$=20 DECLARE =A0loc refcursor; BEGIN =A0IF $3 =3D 1 THEN =A0=A0open loc for =A0=A0 =A0=A0select a.locationid, a.locationname, a.partnerid, b.partnername,=20 a.phone1,a.phone2,=A0=A0=20 =A0=A0a.fax1, a.fax2,a.physaddress1, a.physaddress2, a.physcityid, e.city||= ',=20 '||e.statecode||'=A0 '||e.zipcode, =A0=A0a.contact1, a.contact2, a.email1, a.email2, a.activestatus, a.timezon= e,=20 a.taxrate, e.statecode,a.faxflag, =A0=A0a.ticklerflag,case when a.ticklerflag =3D 't' then 'YES' else 'NO' en= d, e.city =A0=A0from dimension.location_base a, dimension.partner b, postal.us_zip e= =20 =A0=A0where a.partnerid =3D b.partnerid and a.physcityid =3D e.zipid and e.= statecode =3D=20 $2 order by a.locationname; =A0ELSE =A0=A0IF $3 =3D 0 THEN =A0=A0=A0open loc for =A0=A0 =A0=A0=A0select a.locationid, a.locationname, a.partnerid, b.partnername,= =20 a.phone1,a.phone2,=A0=A0=20 =A0=A0=A0a.fax1, a.fax2,a.physaddress1, a.physaddress2, a.physcityid, e.cit= y||',=20 '||e.statecode||'=A0 '||e.zipcode, =A0=A0=A0a.contact1, a.contact2, a.email1, a.email2, a.activestatus, a.time= zone,=20 a.taxrate, e.statecode,a.faxflag, =A0=A0=A0a.ticklerflag,case when a.ticklerflag =3D 't' then 'YES' else 'NO'= end, e.city =A0=A0=A0from dimension.location_base a, dimension.partner b, postal.us_zip= e=20 =A0=A0=A0where a.partnerid =3D b.partnerid and a.physcityid =3D e.zipid and= e.statecode =3D=20 $2=20 =A0=A0=A0and lower(a.locationname) like $1||'%' order by a.locationname;=A0 =A0=A0ELSE =A0=A0=A0open loc for =A0=A0 =A0=A0=A0select a.locationid, a.locationname, a.partnerid, b.partnername,= =20 a.phone1,a.phone2,=A0=A0=20 =A0=A0=A0a.fax1, a.fax2,a.physaddress1, a.physaddress2, a.physcityid, e.cit= y||',=20 '||e.statecode||'=A0 '||e.zipcode, =A0=A0=A0a.contact1, a.contact2, a.email1, a.email2, a.activestatus, a.time= zone,=20 a.taxrate, e.statecode,a.faxflag, =A0=A0=A0a.ticklerflag,case when a.ticklerflag =3D 't' then 'YES' else 'NO'= end, e.city =A0=A0=A0from dimension.location_base a, dimension.partner b, postal.us_zip= e=20 =A0=A0=A0where a.partnerid =3D b.partnerid and a.physcityid =3D e.zipid and= a.partnerid =3D=20 $1; =A0 =A0=A0END IF; =A0END IF; =A0return loc; END; $BODY$ =A0 LANGUAGE plpgsql VOLATILE =A0 COST 100; ALTER FUNCTION dimension.get_location_list(character varying, character var= ying,=20 integer) OWNER TO postgres; GRANT EXECUTE ON FUNCTION dimension.get_location_list(character varying,=20 character varying, integer) TO public; GRANT EXECUTE ON FUNCTION dimension.get_location_list(character varying,=20 character varying, integer) TO postgres; GRANT EXECUTE ON FUNCTION dimension.get_location_list(character varying,=20 character varying, integer) TO "eMenuAdmin"; I am running my test procs from the pgAdminIII GUI.=A0 Is my syntax wrong t= o=20 execute the function?=A0 If I run select test_proc1(3), I do get the correc= t=20 result which is a column header (test_proc1 integer) and a value (2).=A0 So= why=20 wouldn't I get a 6-column result set when running select test_proc(2) ? My java code=A0syntax is as follows: =A0public Collection getLocationList(String pname, String ste, int type) {= =A0=A0=A0 =A0=A0PartnerDAO ef =3D new PartnerDAO(); =A0=A0CallableStatement proc =3D null; =A0=A0Connection conn =3D ef.getConnection(); =A0=A0Collection locations =3D new ArrayList();=A0=A0=A0=A0=A0=A0 =A0=A0try {=A0=A0=A0 =A0=A0=A0proc =3D conn.prepareCall("{ ?=3D call dimension.get_location_list= (?,?,?) }"); =A0=A0=A0proc.registerOutParameter(1, Types.OTHER); =A0=A0=A0=A0=A0 proc.setString(2, pname.toLowerCase().trim()); =A0=A0=A0=A0=A0 proc.setString(3, ste); =A0=A0=A0=A0=A0 proc.setInt(4, type);=A0=A0=20 =A0=A0=A0=A0=A0 conn.setAutoCommit(false); =A0=A0=A0=A0=A0 proc.execute();=A0=A0=A0=A0 =A0 =A0=A0=A0=A0=A0 ResultSet rs =3D (ResultSet) proc.getObject(1); =A0=A0=A0=A0=A0 while (rs.next()) { =A0=A0=A0=A0=A0 =A0LocationVO eRec =3D new LocationVO();=A0=A0=A0=A0=A0 =A0= =A0=A0=A0=A0=A0 =A0 =A0=A0=A0=A0=A0 =A0eRec.setLocationId(rs.getInt(1)); =A0=A0=A0=A0=A0 =A0eRec.setLocationName(rs.getString(2)); =A0=A0=A0=A0=A0 =A0eRec.setPartnerId(rs.getInt(3)); =A0=A0=A0=A0eRec.setPartnerName(rs.getString(4)); =A0=A0=A0=A0eRec.setPhone1(rs.getString(5)); =A0=A0=A0=A0eRec.setDbphone1(rs.getString(5)); =A0=A0=A0=A0eRec.setPhone2(rs.getString(6)); =A0=A0=A0=A0eRec.setDbphone2(rs.getString(6)); =A0=A0=A0=A0eRec.setFax1(rs.getString(7)); =A0=A0=A0=A0eRec.setDbfax1(rs.getString(7)); =A0=A0=A0=A0eRec.setFax2(rs.getString(8)); =A0=A0=A0=A0eRec.setDbfax2(rs.getString(8)); =A0=A0=A0=A0eRec.setAddress1(rs.getString(9)); =A0=A0=A0=A0eRec.setAddress2(rs.getString(10)); =A0=A0=A0=A0eRec.setCityId(rs.getInt(11)); =A0=A0=A0=A0eRec.setCityName(rs.getString(12)); =A0=A0=A0=A0eRec.setContact1(rs.getString(13)); =A0=A0=A0=A0eRec.setDbcontact1(rs.getString(13)); =A0=A0=A0=A0eRec.setContact2(rs.getString(14)); =A0=A0=A0=A0eRec.setDbcontact2(rs.getString(14)); =A0=A0=A0=A0eRec.setEmail1(rs.getString(15)); =A0=A0=A0=A0eRec.setDbemail1(rs.getString(15)); =A0=A0=A0=A0eRec.setEmail2(rs.getString(16)); =A0=A0=A0=A0eRec.setDbemail2(rs.getString(16)); =A0=A0=A0=A0eRec.setStatus(rs.getInt(17)); =A0=A0=A0=A0eRec.setDbstatus(rs.getString(17)); =A0=A0=A0=A0eRec.setTimeZone(rs.getString(18)); =A0=A0=A0=A0eRec.setTaxRate(rs.getDouble(19)); =A0=A0=A0=A0eRec.setDbtaxRate(rs.getDouble(19)); =A0=A0=A0=A0eRec.setStateCode(rs.getString(20)); =A0=A0=A0=A0eRec.setFaxFlag(Boolean.parseBoolean(rs.getString(21))); =A0=A0=A0=A0eRec.setDbfaxFlag(Boolean.parseBoolean(rs.getString(21))); =A0=A0=A0=A0eRec.setTicklerFlag(Boolean.parseBoolean(rs.getString(22))); =A0=A0=A0=A0eRec.setTicklerFlagText(rs.getString(23)); =A0=A0=A0=A0eRec.setScName(rs.getString(24)); =A0=A0=A0=A0eRec.setCopyMenuSourceId(0); =A0=A0=A0=A0=A0 =A0locations.add(eRec);=A0=A0=A0=A0 =A0=A0=A0=A0=A0 }=A0=A0=A0 =A0=A0 =A0=A0} catch (Exception e) { =A0=A0=A0e.printStackTrace(); =A0=A0}finally { =A0=A0=A0clearResources(conn, proc); =A0=A0} =A0=A0return locations;=A0=A0 =A0} If I am not including something specific required to actually display a res= ult=20 set, can you enlighten me?=A0 How would you write a function to return a ro= w from=20 the test data I provided?=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.=A0 The original application was based on postgresq= l 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: <unna= med > portal n>, where n seems to indicate how many times I have run a cursor f= rom > the SQL window. The example you give acts exactly as I would expect, ie, it returns the generated name of a cursor.=A0 And it does so in every release back to at least 8.0, not just 9.0.=A0 So I think you've simplified your example to the point that it no longer demonstrates whatever problem you're actually having. =A0=A0=A0 =A0=A0=A0 =A0=A0=A0 regards, tom lane
pgsql-bugs by date: