Re: Is the PL/pgSQL refcursor useful in a modern three-tier app? - Mailing list pgsql-general

On 3/15/23 18:41, Bryn Llewellyn wrote:
>> adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com> wrote:
>>
>> I have a hard time fathoming why someone who writes documentation does 
>> not actually read documentation.
> 
> Ouch. In fact, I had read the whole of the "43.7. Cursors" section in 
> the "PL/pgSQL" chapter (www.postgresql.org/docs/15/plpgsql-cursors.html 
> <http://www.postgresql.org/docs/15/plpgsql-cursors.html>). And the 
> sections in the "SQL Commands" chapter for "declare", "fetch" and 
> "close". But several of the key concepts didn't sink in and this 
> prevented me not only from understanding what some of the examples 
> showed but, worse, from being able to use the right vocabulary to 
> express what confused me.

Given this from your original question:

" (Anyway, without anything like Oracle PL/SQL's packages, you have no 
mechanism to hold the opened cursor variable between successive server 
calls.)"


What part of this:

CREATE TABLE test (col text);
INSERT INTO test VALUES ('123');

CREATE FUNCTION reffunc(refcursor) RETURNS refcursor AS '
BEGIN
     OPEN $1 FOR SELECT col FROM test;
     RETURN $1;
END;
' LANGUAGE plpgsql;

BEGIN;
SELECT reffunc('funccursor');
FETCH ALL IN funccursor;
COMMIT;

did not make sense in that context?


> The open portal instances in a particular session are listed in 
> pg_cursors. (Why not pg_portals?) When the instance was created with the 

Why are tables also known as relations and you can look them up in 
pg_class or pg_tables?

Answer: It is the rules of the game.



> 
> create procedure s.p()
>    set search_path = pg_catalog, pg_temp
>    language plpgsql
> as $body$
> declare
>    "My Refcursor" cursor for select k, v from s.t order by k;
> begin
>    open "My Refcursor";
> *  raise info '%', pg_typeof("My Refcursor")::text;*
> end;
> $body$;
> 
> begin;
> call s.p();
> select name, statement from pg_cursors;
> fetch forward 5 in "My Refcursor";
> end;
> 
> (I included "pg_typeof()" just here to make the point that it reports 
> "refcursor" and not the plain "cursor" that the declaration might lead 
> you to expect. It reports "refcursor" in all the other PL/pgSQL examples 
> too.

https://www.postgresql.org/docs/current/plpgsql-cursors.html

"All access to cursors in PL/pgSQL goes through cursor variables, which 
are always of the special data type refcursor. One way to create a 
cursor variable is just to declare it as a variable of type refcursor. 
Another way is to use the cursor declaration syntax, which in general is:

name [ [ NO ] SCROLL ] CURSOR [ ( arguments ) ] FOR query;
"

Again, I would like to know how that is confusing?


> 
> With all these variants (and there may be more), and with only some of 
> the exemplified, I don't feel too stupid for getting confused.
> 


Where you get confused is in moving the goal posts.

What starts out with:

"(Anyway, without anything like Oracle PL/SQL's packages, you have no 
mechanism to hold the opened cursor variable between successive server 
calls.)


Is it fair to say that the PL/pgSQL refcursor is useful, at best, only 
in very special use-cases?"

evolves into deep dive into all thing cursors.

-- 
Adrian Klaver
adrian.klaver@aklaver.com