Re: 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
>