Re: BUG #5753: Existing Functions No Longer Work

2010/11/16 vince maxey <vamax27@yahoo.com>:
> Here is my website:=C2=A0 www.emenusonline.net=C2=A0 It appears that the =
pg=C2=A0version my web
> host company uses is 8.1.3
>
> Right now=C2=A0this site is=C2=A0in beta mode, so you can register and or=
der food
> and=C2=A0there will be no repercussions, other than you will receive an e=
mail
> indicating you did so.=C2=A0 (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 scree=
n shot
> is of the function that would be called to determine whether you are an e=
xisting
> valid user or not.=C2=A0 This site exists for real, gentlemen and it work=
s; 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?=C2=A0 So i=
nstead of
> entering ' select get_user_login_info('vamax27') '=C2=A0 I should type:=
=C2=A0 =C2=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:=C2=A0 <unnam=
ed portal 1>
> ,=C2=A0 <unnamed portal 2>,=C2=A0 <unnamed portal 3>=C2=A0 etc?
>
> portal=C2=A0 NOT=C2=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.=C2=A0=C2=A0I know my data is s=
et up properly
> and I know my code is good.
>
> Screenshot2 is the results of my running select refcursor_function=C2=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').=C2=A0 These are all being run from phppgA=
dmin 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=C2=A0function I submitted and either prove or disprove what I am say=
ing; 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.=C2=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; and
>>
>> they are not working within my application.=C2=A0 They used to work.
>
> That's basically impossible to believe, because the behavior on this
> point didn't change.=C2=A0 There are certainly some incompatibilities bet=
ween
> 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.=C2=A0 So I think you're barking up t=
he
> 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.=C2=A0 Can you repro=
duce
> a change of behavior using just psql?
>
> =C2=A0=C2=A0=C2=A0 =C2=A0=C2=A0=C2=A0 =C2=A0=C2=A0=C2=A0 regards, tom lane
>