On 3/15/23 18:41, Bryn Llewellyn wrote:
adrian.klaver@xxxxxxxxxxx <mailto:adrian.klaver@xxxxxxxxxxx> 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@xxxxxxxxxxx
