Re: BUG #5753: Existing Functions No Longer Work - Mailing list pgsql-bugs
From | Pavel Stehule |
---|---|
Subject | Re: BUG #5753: Existing Functions No Longer Work |
Date | |
Msg-id | AANLkTimh=DyHTDouNROPqMSASV8v2fNgQ1sx0NDxdByL@mail.gmail.com Whole thread Raw |
In response to | Re: BUG #5753: Existing Functions No Longer Work (vince maxey <vamax27@yahoo.com>) |
List | pgsql-bugs |
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 >
pgsql-bugs by date: