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

On 3/15/23 13:37, Bryn Llewellyn wrote:
>> laurenz(dot)albe(at)cybertec(dot)at wrote:
>>

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

Huh?

You provided your own example earlier:

"Of course, it all falls into place now. I can see how I could write a
client app in, say, Python to write a humongous report to a file by
fetching manageably-sized chunks, time and again until done with a
function like my "g()" here, from a cursor that I'd opened using a
function like my "f()"."

>
>
>
>
>
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com