public inbox for [email protected]  

help / color / mirror / Atom feed

From: Maximilian Tyrtania <[email protected]>
To: [email protected]
Subject: Re: Problem with refcursor
Date: Tue, 9 Jan 2024 12:17:06 +0100
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>

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)  latest in thread

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]
  Subject: Re: Problem with refcursor
  In-Reply-To: <[email protected]>

* 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