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.=A0=20

You are speaking to me entirely in abstract, where I have provided you with=
=20
several concrete examples of functions that work when called from the java=
=20
application.=A0 I also provided you evidence that using the terms in your=
=20
documentation do not work.=A0 What am I doing wrong?

Again, I'm imploring you both:=A0 Use the data I provided to create a test =
table.=A0=20
Create the function I have created (test_proc and test_proc1) and run it or=
=20
re-write it so that it works and please provide that back to me along with =
the=20
syntax I should use to make it work.

Please, prove your documentation applies to=A0my data because as it stands =
from my=20
perspective your documentation is fairly poor in explaining how to write an=
d=20
call functions, considering they are a fairly common tool for developers an=
d=20
DBAs.=A0 Why can I not Google for postgreSQL function examples?=A0 I submit=
 it=A0might=20
be=A0for this reason: perhaps people stay away from them because they have =
flaws,=20
either in how they have been communicated or in how they are invoked....

I've worked extensively with SQL Server, IBM, Sybase and Oracle.=A0 I know =
how to=20
write and test stored procedures.=A0=20


I'm telling you that something is different between 8.1 and 9.0 and all you=
 are=20
offering are conceptual excerpts from your own documentation.=A0=20


Please put it back to me using my data and you might get some insight into =
what=20
could be tweaked with your documentation so that it can be more intuitive a=
nd=20
functions used more often....

=A0


----- Original Message ----
From: Pavel Stehule <pavel.stehule@gmail.com>
To: vince maxey <vamax27@yahoo.com>
Cc: Tom Lane <tgl@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 <vamax27@yahoo.com>:
> Here is my website:=A0 www.emenusonline.net=A0 It appears that the pg=A0v=
ersion my=20
>web
> host company uses is 8.1.3
>
> Right now=A0this site is=A0in beta mode, so you can register and order fo=
od
> and=A0there will be no repercussions, other than you will receive an email
> indicating you did so.=A0 (No money will be collected, cc information or=
=20
anything
> - use fake everything other than email address)
>
> If you were to register on this site and then log in, the following scree=
n=20
shot
> is of the function that would be called to determine whether you are an=
=20
>existing
> valid user or not.=A0 This site exists for real, gentlemen and it works; =
or=20
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?=A0 So inst=
ead of
> entering ' select get_user_login_info('vamax27') '=A0 I should type:=A0 =
=A0FETCH 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:=A0 <unnamed =
portal=20
>1>
> ,=A0 <unnamed portal 2>,=A0 <unnamed portal 3>=A0 etc?
>
> portal=A0 NOT=A0 cursor
>
> I have lots of these functions written using the identical syntax in rega=
rd to
> refcursors and they all work on this site.=A0=A0I know my data is set up =
properly
> and I know my code is good.
>
> Screenshot2 is the results of my running select refcursor_function=A0 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').=A0 These are all being run from phppgAdmi=
n 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=A0function 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 ab=
le to
> do for myself.=A0 I'm out of ideas, Tom.
>
> 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: Tue, November 16, 2010 11:31:28 AM
> Subject: Re: [BUGS] BUG #5753: Existing Functions No Longer Work
>
> vince maxey <vamax27@yahoo.com> writes:
>> My functions all worked prior to switching to 9.0 and I can excute funct=
ions
>> from the pgAdminIII UI which return an individual result, such as an int=
eger,
>> but cannot test/troubleshoot those which use refcursors in the same mann=
er;=20
>and
>>
>> they are not working within my application.=A0 They used to work.
>
> That's basically impossible to believe, because the behavior on this
> point didn't change.=A0 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.=A0 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.=A0 Can you reproduce
> a change of behavior using just psql?
>
> =A0=A0=A0 =A0=A0=A0 =A0=A0=A0 regards, tom lane
>