Re: 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(dot)stehule(at)gmail(dot)com>
To: vince maxey <vamax27(at)yahoo(dot)com>
Cc: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>; pgsql-bugs(at)postgresql(dot)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 <vamax27(at)yahoo(dot)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 <tgl(at)sss(dot)pgh(dot)pa(dot)us>
> To: vince maxey <vamax27(at)yahoo(dot)com>
> Cc: pgsql-bugs(at)postgresql(dot)org
> Sent: Tue, November 16, 2010 11:31:28 AM
> Subject: Re: [BUGS] BUG #5753: Existing Functions No Longer Work
>
> vince maxey <vamax27(at)yahoo(dot)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
>