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

On 3/15/23 18:41, Bryn Llewellyn wrote:
>> adrian(dot)klaver(at)aklaver(dot)com <mailto:adrian(dot)klaver(at)aklaver(dot)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(dot)klaver(at)aklaver(dot)com