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

On 3/15/23 13:37, Bryn Llewellyn wrote:
>> laurenz.albe@cybertec.at wrote:
>>

> Re « You seem to think that a client request corresponds to a single database request », I meant no more than what
psqlmodels when you hit "return"after terminating an ordinary SQL statement with semi-colon (i.e. not a meta-command).
Iused "client" to mean a program that connects directly to the PostgreSQL server (using TCP/IP)—and not the ultimate
humanclient who's using, say, a browser. Perhaps I should have said "top-level SQL call". (To keep this simple, let's
assumethat 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
factthat 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
itfrom 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
concreteuse case where, not withstanding the "where" restriction that my "select" used, I can't tell how much of the
resultset I'll need or where reading result #n1 informs me that I next need to scroll and read result #n2. So I was
lookingfor 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.klaver@aklaver.com