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

> 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) 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.

It's very much clearer now than when I started this thread, about twenty-four hours ago. Here's (some of) what I believe that I now understand.

"refcursor" is a base type, listed in pg_type. This sentence seems to be key:

«
A refcursor value is simply the string name of a so-called portal containing the active query for the cursor. This name can be passed around, assigned to other refcursor variables, and so on, without disturbing the portal.
»

Maybe it's better to say that a portal has a defining "select" statement and acts as a pointer to the potential result set that its select statement defines. A portal also represents the position of the current (next-to-be-fetched) row it that set. The doc that I've found doesn't make it clear how much of the entire result set is materialized at a time. But the implication is that it's materialized only in portions and that one portion is purged to make room for another.

You can create a portal instance using either top-level SQL (with the "declare" statement) or using PL/pgSQL by declaring a refcursor variable for its name and then using that as the argument of "open". Only in top-level SQL, the "with hold" option for "declare" lets you create a portal instance outside of a transaction block. This has session duration. (Or you can pre-empt this with the "close" statement.) Otherwise, you must use the "declare" statement within an ongoing transaction. With this choice, it vanishes when the transaction ends. You can also create a portal instance by using PL/pgSQL. (There's no "with hold" option here.)

A portal instance exists within the session as a whole, even though you can declare the refcursor to denote it as a PL/pgSQL subprogram's formal parameter or as a PL/pgSQL local variable. This means that you can create a portal instance using PL/pgSQL and (when you know its name) fetch from it using top-level SQL

The open portal instances in a particular session are listed in pg_cursors. (Why not pg_portals?) When the instance was created with the SQL "declare" statement, pg_cursors.statement shows the verbatim text that follows the "declare" keyword. (In other words, not a legal SQL statement.) When the instance was created using PL/pgSQL, pg_cursors.statement shows the verbatim text that follows (in one creation approach variant) "open <identifier> for" in the defining block statement's executable section. (In other words, and with a caveat about placeholders, this is a legal SQL statement.)

A portal instance is uniquely identified by just its name. (You cannot use a schema-qualified identifier to create it or to refer to it.) And (just like a prepared statement) the name must be unique only within a particular session.

There are many ways to set the name of a portal instance. Here are some examples. First top-level SQL:

begin;
declare "My Refcursor" cursor for select k, v from s.t order by k;
select name, statement from pg_cursors;
fetch forward 5 in "My Refcursor";
end;

I noticed that 'select pg_typeof("My Refcursor")' within the ongoing txn fails with '42703: column "My Refcursor" does not exist'.

Now, PL/pgSQL:

create function s.f(cur in refcursor = 'cur')
returns refcursor
set search_path = pg_catalog, pg_temp
language plpgsql
as $body$
begin
open cur for select k, v from s.t order by k;
return cur;
end;
$body$;

begin;
select s.f('My Cursor');
select name, statement from pg_cursors;
fetch forward 5 in "My Cursor";
end;

Arguably, it's pointless to use a function to return the name of the portal instance that you supplied as an input—and you might just as well write this:

create procedure s.p(cur in refcursor = 'cur')
set search_path = pg_catalog, pg_temp
language plpgsql
as $body$
begin
open cur for select k, v from s.t order by k;
end;
$body$;

begin;
call s.p('My Cursor');
select name, statement from pg_cursors;
fetch forward 5 in "My Cursor";
end;

You could sacrifice the ability to name the portal instance at runtime like this:

create procedure s.p()
set search_path = pg_catalog, pg_temp
language plpgsql
as $body$
declare
cur refcursor := 'My Cursor';
begin
open cur for select k, v from s.t order by k;
end;
$body$;

begin;
call s.p();
select name, statement from pg_cursors;
fetch forward 5 in "My Cursor";
end;

You can even let the runtime system make up a name for you. But you need to go back to the function encapsulation to learn what was chosen:

create function s.f()
returns refcursor
set search_path = pg_catalog, pg_temp
language plpgsql
as $body$
declare
cur refcursor;
begin
open cur for select k, v from s.t order by k;
return cur;
end;
$body$;

begin;
select s.f();
select name, statement from pg_cursors;
fetch forward 5 in "<unnamed portal 1>";
end;

Here's yet another variant:

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.

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.