Re: [BUGS] BUG #5753: Existing Functions No Longer Work
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? 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
Re: [BUGS] BUG #5753: Existing Functions No Longer Work
vince maxey vama...@yahoo.com writes: 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. That's basically impossible to believe, because the behavior on this point didn't change. There are certainly some incompatibilities between 9.0 and previous releases, but nothing about refcursor-returning functions specifically; and AFAICT the example functions you provided work just the same in 9.0 as before. So I think you're barking up the wrong tree and the issue is something else than you think. Perhaps you could put together a complete test case (not just the function, but including its call) that works in 8.4 and not in 9.0? BTW, if the gripe is specifically about what happens in the pgAdmin UI, another possible explanation is that pgAdmin changed. Can you reproduce a change of behavior using just psql? 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
Re: [BUGS] BUG #5753: Existing Functions No Longer Work
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;
Re: [BUGS] BUG #5753: Existing Functions No Longer Work
2010/11/16 vince maxey vama...@yahoo.com:
Here is my website: www.emenusonline.net It appears that the pg version my
web
host company uses is 8.1.3
Right now this site is in beta mode, so you can register and order food
and there will be no repercussions, other than you will receive an email
indicating you did so. (No money will be collected, cc information or
anything
- use fake everything other than email address)
If you were to register on this site and then log in, the following screen
shot
is of the function that would be called to determine whether you are an
existing
valid user or not. This site exists for real, gentlemen and it works; or
after
you register you will never be able to log back in to the site.
I reviewed the documenation referenced by Pavel and if I'm understanding it,
this is only to invoke a cursor from the pgAdmin GUI, correct? So instead of
entering ' select get_user_login_info('vamax27') ' I should type: FETCH ALL
IN unnamed cursor 1;
If that is the case then why is it that each time I type select
get_user_login_info('vamax27') the result increments by one: unnamed portal
1
, unnamed portal 2, unnamed portal 3 etc?
portal NOT cursor
I have lots of these functions written using the identical syntax in regard to
refcursors and they all work on this site. I know my data is set up properly
and I know my code is good.
Screenshot2 is the results of my running select refcursor_function and
screenshot3 is the results of my running fetch all in unnamed portal 1 and
finally screenshot4 is my trying to run fetch all in
get_user_login_info('vamax27'). These are all being run from phppgAdmin on my
web host provider's site.
With all due respect, guys, what you are telling me doesn't add up.
you cannot to fetch data from function that returns a refcursor.
refcursor is varchar - name of some object (cursor). when you open
cursor, then you can specify a name (it's refcursor), but when you
fetch data, you must to use a direct sql identifier of cursor - not a
reference.
simply, you can't to write
fetch all from function(parameter);
Pavel
It would take you minutes to set up the test data I provided and run the
test function I submitted and either prove or disprove what I am saying; or
re-write the syntax so that it does return results and show me how I'm a fool
and wasting your time; at which point I will apologize profusely :)
I'm not trying to give you a hard time or make you do work I should be able to
do for myself. I'm out of ideas, Tom.
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: Tue, November 16, 2010 11:31:28 AM
Subject: Re: [BUGS] BUG #5753: Existing Functions No Longer Work
vince maxey vama...@yahoo.com writes:
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.
That's basically impossible to believe, because the behavior on this
point didn't change. There are certainly some incompatibilities between
9.0 and previous releases, but nothing about refcursor-returning
functions specifically; and AFAICT the example functions you provided
work just the same in 9.0 as before. So I think you're barking up the
wrong tree and the issue is something else than you think.
Perhaps you could put together a complete test case (not just the
function, but including its call) that works in 8.4 and not in 9.0?
BTW, if the gripe is specifically about what happens in the pgAdmin UI,
another possible explanation is that pgAdmin changed. Can you reproduce
a change of behavior using just psql?
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
Re: [BUGS] BUG #5753: Existing Functions No Longer Work
/*
you cannot to fetch data from function that returns a refcursor.
refcursor is varchar - name of some object (cursor). when you open
cursor, then you can specify a name (it's refcursor), but when you
fetch data, you must to use a direct sql identifier of cursor - not a
reference.
simply, you can't to write
fetch all from function(parameter);
*/
Please explain to me then why my application works.
You are speaking to me entirely in abstract, where I have provided you with
several concrete examples of functions that work when called from the java
application. I also provided you evidence that using the terms in your
documentation do not work. What am I doing wrong?
Again, I'm imploring you both: Use the data I provided to create a test
table.
Create the function I have created (test_proc and test_proc1) and run it or
re-write it so that it works and please provide that back to me along with the
syntax I should use to make it work.
Please, prove your documentation applies to my data because as it stands from
my
perspective your documentation is fairly poor in explaining how to write and
call functions, considering they are a fairly common tool for developers and
DBAs. Why can I not Google for postgreSQL function examples? I submit
it might
be for this reason: perhaps people stay away from them because they have flaws,
either in how they have been communicated or in how they are invoked
I've worked extensively with SQL Server, IBM, Sybase and Oracle. I know how to
write and test stored procedures.
I'm telling you that something is different between 8.1 and 9.0 and all you are
offering are conceptual excerpts from your own documentation.
Please put it back to me using my data and you might get some insight into what
could be tweaked with your documentation so that it can be more intuitive and
functions used more often
- Original Message
From: Pavel Stehule pavel.steh...@gmail.com
To: vince maxey vama...@yahoo.com
Cc: Tom Lane t...@sss.pgh.pa.us; pgsql-bugs@postgresql.org
Sent: Tue, November 16, 2010 1:14:14 PM
Subject: Re: [BUGS] BUG #5753: Existing Functions No Longer Work
2010/11/16 vince maxey vama...@yahoo.com:
Here is my website: www.emenusonline.net It appears that the pg version my
web
host company uses is 8.1.3
Right now this site is in beta mode, so you can register and order food
and there will be no repercussions, other than you will receive an email
indicating you did so. (No money will be collected, cc information or
anything
- use fake everything other than email address)
If you were to register on this site and then log in, the following screen
shot
is of the function that would be called to determine whether you are an
existing
valid user or not. This site exists for real, gentlemen and it works; or
after
you register you will never be able to log back in to the site.
I reviewed the documenation referenced by Pavel and if I'm understanding it,
this is only to invoke a cursor from the pgAdmin GUI, correct? So instead of
entering ' select get_user_login_info('vamax27') ' I should type: FETCH ALL
IN unnamed cursor 1;
If that is the case then why is it that each time I type select
get_user_login_info('vamax27') the result increments by one: unnamed portal
1
, unnamed portal 2, unnamed portal 3 etc?
portal NOT cursor
I have lots of these functions written using the identical syntax in regard to
refcursors and they all work on this site. I know my data is set up properly
and I know my code is good.
Screenshot2 is the results of my running select refcursor_function and
screenshot3 is the results of my running fetch all in unnamed portal 1 and
finally screenshot4 is my trying to run fetch all in
get_user_login_info('vamax27'). These are all being run from phppgAdmin on my
web host provider's site.
With all due respect, guys, what you are telling me doesn't add up.
you cannot to fetch data from function that returns a refcursor.
refcursor is varchar - name of some object (cursor). when you open
cursor, then you can specify a name (it's refcursor), but when you
fetch data, you must to use a direct sql identifier of cursor - not a
reference.
simply, you can't to write
fetch all from function(parameter);
Pavel
It would take you minutes to set up the test data I provided and run the
test function I submitted and either prove or disprove what I am saying; or
re-write the syntax so that it does return results and show me how I'm a fool
and wasting your time; at which point I will apologize profusely :)
I'm not trying to give you a hard time or make you do work I should be able to
do for myself. I'm out of ideas, Tom.
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: Tue, November 16, 2010 11:31:28 AM
Subject: Re: [BUGS] BUG #5753: Existing Functions No
Re: [BUGS] BUG #5753: Existing Functions No Longer Work
/* you cannot to fetch data from function that returns a refcursor. refcursor is varchar - name of some object (cursor). when you open cursor, then you can specify a name (it's refcursor), but when you fetch data, you must to use a direct sql identifier of cursor - not a reference. simply, you can't to write fetch all from function(parameter); */ Please explain to me then why my application works. You are speaking to me entirely in abstract, where I have provided you with several concrete examples of functions that work when called from the java application. I also provided you evidence that using the terms in your documentation do not work. What am I doing wrong? Again, I'm imploring you both: Use the data I provided to create a test table. Create the function I have created (test_proc and test_proc1) and run it or re-write it so that it works and please provide that back to me along with the syntax I should use to make it work. Please, prove your documentation applies to my data because as it stands from my perspective your documentation is fairly poor in explaining how to write and call functions, considering they are a fairly common tool for developers and DBAs. Why can I not Google for postgreSQL function examples? I submit it might be for this reason: perhaps people stay away from them because they have flaws, either in how they have been communicated or in how they are invoked I've worked extensively with SQL Server, IBM, Sybase and Oracle. I know how to write and test stored procedures. I'm telling you that something is different between 8.1 and 9.0 and all you are offering are conceptual excerpts from your own documentation. Please put it back to me using my data and you might get some insight into what could be tweaked with your documentation so that it can be more intuitive and functions used more often Vince - is there any chance that you were using a non-standard PostgreSQL distribution or driver before? Like something from EnterpriseDB? It may be that a third-party JDBC driver was doing some behind-the-curtains work on your behalf. -- Korry --- Korry Douglas Senior Database Dude EnterpriseDB Corporation The Enterprise Postgres Company Phone: (804)241-4301 Mobile: (620) EDB-NERD -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5753: Existing Functions No Longer Work
On Tue, 16 Nov 2010, Korry Douglas wrote: Vince - is there any chance that you were using a non-standard PostgreSQL distribution or driver before? Like something from EnterpriseDB? It may be that a third-party JDBC driver was doing some behind-the-curtains work on your behalf. The stock JDBC driver does have support for refcursors, so that it will do FETCH ALL FROM it and return a ResultSet object instead of just a string if you call getObject on a refcursor value. This thread is too confusing with random snippets, assertions, and differences between pgadmin, psql, and other interfaces to tell what's really going on. The behavior of the JDBC driver shouldn't have changed between releases either, but this may explain the differences seen between pgadmin and the user's application. http://jdbc.postgresql.org/documentation/84/callproc.html#callproc-resultset-refcursor Kris Jurka -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5753: Existing Functions No Longer Work
On 17/11/10 05:42, vince maxey wrote: You are speaking to me entirely in abstract, where I have provided you with several concrete examples of functions that work when called from the java application. I've only been following this thread broadly, but I haven't seen a complete and self-contained test case (Java example code, and SQL database creation script) that demonstrates the issue yet. Where's the executable .jar and source .zip ? When I had issues with PostgreSQL and JDBC (specifically, with client certificate support) I wrote a simple, self-contained test case that clearly demonstrated the problem. This meant that everybody could test it easily, could see what was going on, and was working with the same thing. Getting from there to a fix was much easier as a result. Consider doing the same. I'd be testing and trying to help if there was enough information in this thread to do so without spending lots of my time on it - time I'm not working for pay, or doing something more fun. I'm telling you that something is different between 8.1 and 9.0 and all you are offering are conceptual excerpts from your own documentation. You've offered only incomplete views of part of what you're doing too, so I'd hesitate to push this too hard onto the people who've been spending their time trying to help you. Your original post doesn't provide the code that's executing the queries, and your follow-up would still require a bunch of work to turn into an executable test case. Tom in particular doesn't do all that much with Java, and probably won't be taking the time to write a test-case for you. Even now, I can't find any mention of the JDBC driver version you're using in these tests. I've worked extensively with SQL Server, IBM, Sybase and Oracle. I know how to write and test stored procedures. Unfortunately, PostgreSQL does not really support stored procedures. It supports stored *functions* that run within the context of a normal SQL statement, but your function still runs within a SELECT and is restricted in its control over transactions among other things. The JDBC driver maps the JDBC stored procedure call syntax to call a Pg stored function. This works rather well, though I hate to think how it'll be handled when real stored procedures are implemented at some point. I'm not sure this matters much in the context of your current issue, but it's worth bearing in mind. -- System Network Administrator POST Newspapers -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
