hello 2010/11/16 vince maxey <vama...@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=# 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 vama...@yahoo.com > To: Tom Lane <t...@sss.pgh.pa.us> > Cc: Me Yahoo <vama...@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 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 <t...@sss.pgh.pa.us> > To: Vince Maxey <vama...@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" <vama...@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. 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@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-bugs > -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
