Re: Problem with refcursor - Mailing list pgsql-sql
| From | Samed YILDIRIM |
|---|---|
| Subject | Re: Problem with refcursor |
| Date | |
| Msg-id | CAAo1mbmFccYBL37seno9EqYuTSgr4=bFdDXQ=ndDsQoT85Az-A@mail.gmail.com Whole thread Raw |
| In response to | Re: Problem with refcursor (Maximilian Tyrtania <maximilian.tyrtania@inqua-institut.de>) |
| List | pgsql-sql |
Hi Maximilian,
It has been a while since you sent the e-mail. I hope you have already fixed the problem.
I think the issue is the way you tried to loop over the refcursor.
FOR curClient IN FETCH ALL FROM p_clients LOOP
I haven't tested. But, I think you should update your loop like this
LOOP
FETCH p_clients INTO curClient;
EXIT WHEN NOT FOUND;
On Tue, 9 Jan 2024 at 13:17, Maximilian Tyrtania <maximilian.tyrtania@inqua-institut.de> wrote:
Oops, of course I messed with the outer message before sending it to the list, sorry for that, so the actual error message is:
ERROR: cannot open FETCH query as cursor
CONTEXT: PL/pgSQL function f_client_getcoachingsuccessrate(refcursor) line 10 at FOR over SELECT rows
SQL statement "Select rates.successrate,rates.unclearrate,rates.failrate from f_client_getCoachingsuccessrate(invitedClients) rates"
PL/pgSQL function f_client_get3rdfeedbacksuccessrate() line 7 at SQL statement
I am using PG 16.1 btw.
Max
> Am 09.01.2024 um 09:23 schrieb Maximilian Tyrtania <maximilian.tyrtania@inqua-institut.de>:
>
> Hi there,
>
> I’m running into trouble with ref cursors.
>
> I’ve got these 2 functions, this inner one:
>
> CREATE or replace FUNCTION f_client_getCoachingsuccessrate(p_clients refcursor,out successrate numeric, out unclearrate numeric, out failrate numeric) AS $$
> DECLARE
> curClient record;
> vNumberOfClients bigint;
> vSuccessCounter BIGINT=0;
> vUnclearCounter BIGINT=0;
> vFailureCounter BIGINT=0;
> vCurSuccessState boolean;
> BEGIN
> FOR curClient IN FETCH ALL FROM p_clients LOOP
> —some processing
> END LOOP;
> successrate=f_bigint_getpercentage(vSuccessCounter,vNumberOfClients);
> unclearrate=f_bigint_getpercentage(vUnclearCounter,vNumberOfClients);
> failrate=f_bigint_getpercentage(vFailureCounter,vNumberOfClients);
> */ END;
> $$ LANGUAGE plpgsql;
>
>
> …and this outer one:
>
> create or replace function f_client_get3rdFeedbacksuccessrate(out successrate numeric, out unclearrate numeric, out failrate numeric) as
> $$
> DECLARE
> invitedClients refcursor;
> BEGIN
> open invitedClients FOR SELECT c.* FROM client c join email e on e.client_id=c.id where e.textblock_id=340;
> --raise notice 'all is fine so far';
> Select rates.successrate,rates.unclearrate,rates.failrate from f_client_getCoachingsuccessrate(invitedClients) rates into successrate,unclearrate ,failrate;
> end;
> $$
> LANGUAGE plpgsql;
>
> Now, calling the outer one like this:
>
> select * from f_client_get3rdFeedbacksuccessrate();
>
> results in:
>
> Query 1 ERROR at Line 1: : ERROR: cannot open FETCH query as cursor
> CONTEXT: PL/pgSQL function f_client_getcoachingsuccessrate(refcursor) line 10 at FOR over SELECT rows
> SQL statement "SELECT f_client_getCoachingsuccessrate(invitedClients)"
> PL/pgSQL function f_client_get3rdfeedbacksuccessrate() line 8 at PERFORM
>
> Any pointers?
>
> Thanks, Max
>