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

> laurenz(dot)albe(at)cybertec(dot)at wrote:
>
>> bryn(at)yugabyte(dot)com wrote:
>>
>> Section "43.7. Cursors” in the PL/pgSQL chapter of the doc
>> (www.postgresql.org/docs/current/plpgsql-cursors.html#PLPGSQL-CURSOR-DECLARATIONS) starts with this:
>>
>> «
>> [...]
>> A more interesting usage is to return a reference to a cursor that a function has created, allowing the caller to read the rows. This provides an efficient way to return large row sets from functions.
>> »
>>
>> I can't convince myself that this division of labor is useful. And especially I can't convince myself that the "pipeling" capability is relevant in a three-tier app with a stateless browser UI.
>
> You seem to think that a client request corresponds to a single database request, but that doesn't have to be the case. Satisfying a client request can mean iterating through a result set.
>
> Cursors shine wherever you need procedural processing of query results, or where you don't need the complete result set, but cannot tell in advance how much you will need, or where you need to scroll and move forward and backward through a result set.

Re « You seem to think that a client request corresponds to a single database request », I meant no more than what psql models when you hit "return"after terminating an ordinary SQL statement with semi-colon (i.e. not a meta-command). I used "client" to mean a program that connects directly to the PostgreSQL server (using TCP/IP)—and not the ultimate human client who's using, say, a browser. Perhaps I should have said "top-level SQL call". (To keep this simple, let's assume that I don't use "backslash-semicolon" to send two or more top-level SQL calls in one round trip.)

So in this example:

begin;
declare cur cursor for select k, v from s.t order by k;
fetch forward 10 in cur;
fetch absolute 90 in cur;
fetch forward 10 in cur;
commit;

where I hit "return" after each semicolon, I'd say that I made six top-level SQL calls—and that lines up with the fact that each of "begin". "declare", "fetch", and "commit" has its own page in the "SQL Commands" section of the PG doc.

It sounds like you prefer "database request" for this. Is that right?

I was trying to get at the distinction between issuing a particular SQL statement as a top-level SQL call and issuing it from a PL/pgSQL subprogram.

I've read wording like what you wrote following "Cursors shine wherever you need …" elsewhere. But I can't picture a concrete use case where, not withstanding the "where" restriction that my "select" used, I can't tell how much of the result set I'll need or where reading result #n1 informs me that I next need to scroll and read result #n2. So I was looking for a convincing example.