> laurenz.albe@cybertec.at wrote:
>
>> bryn@yugabyte.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
readthe 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
througha result set.
Re « You seem to think that a client request corresponds to a single database request », I meant no more than what psql
modelswhen 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
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 fact
thateach 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.