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

I may have been one of the respondents who showed some annoyance, and I am sorry
for that. I understand that you ask questions to gain deeper understanding.

On Mon, 2023-03-20 at 13:46 -0700, Bryn Llewellyn wrote:
> Oracle Database doesn't expose scrollability for PL/SQL's equivalent of "refcursor".
> So I had never come across use cases where this was beneficial. I wanted, therefore,
> to hear about some. I thought that insights here would help me understand the mechanics.
> But I didn't get anything beyond "Scrollability is what it is. If you don't need it,
> don't use it."

I recently used cursor scrollability, so I can show you a use case:
https://github.com/cybertec-postgresql/db_migrator/blob/master/db_migrator--1.0.0.sql#L49

The goal is to get the query result count right away, without having to run
a second query for it: you declare the cursor, move to the end of the result set,
fetch the ROW_COUNT, then move back to the beginning of the result set and start
fetching the result rows.

About your description of the difficulties with the terms "cursor", "portal",
"bound" vs. "unbound" cursors in PL/pgSQL etc: I understand your confusion, and I
believe that the documentation could be improved.

The way I understand it, "portal" is PostgreSQL jargon. A portal is a cursor.
The documentation tries to avoid "portal" as an implementation detail.
PL/pgSQL cursors and "refcursor"s are not the same as SQL cursors: they are
variables that hold a cursor name. That is confusing.

I personally find that reading the PostgreSQL documentation gets you far, but only
so far: for deep understanding, you have to read the code. It is usually well
documented and readable, and I have come to see it as an extension of the
documentation that covers the details.

Yours,
Laurenz Albe