> [email protected] 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.
