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

On Tue, 2023-03-14 at 17:50 -0700, Bryn Llewellyn 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.

Yours,
Laurenz Albe