public inbox for [email protected]  

help / color / mirror / Atom feed

From: Samed YILDIRIM <[email protected]>
To: Maximilian Tyrtania <[email protected]>
Cc: [email protected]
Subject: Re: Problem with refcursor
Date: Wed, 31 Jan 2024 16:59:00 +0200
Message-ID: <CAAo1mbmFccYBL37seno9EqYuTSgr4=bFdDXQ=ndDsQoT85Az-A@mail.gmail.com> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>
	<[email protected]>

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;

Refs:

   -
   https://www.postgresql.org/docs/16/plpgsql-cursors.html#PLPGSQL-CURSOR-USING-FETCH
   -
   https://www.postgresql.org/docs/16/plpgsql-control-structures.html#PLPGSQL-CONTROL-STRUCTURES-LOOPS-...


Best regards.
Samed YILDIRIM


On Tue, 9 Jan 2024 at 13:17, Maximilian Tyrtania <
[email protected]> 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 <
> [email protected]>:
> >
> > 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
> >
>
>
>
>

view thread (3+ messages)

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected]
  Subject: Re: Problem with refcursor
  In-Reply-To: <CAAo1mbmFccYBL37seno9EqYuTSgr4=bFdDXQ=ndDsQoT85Az-A@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox