Re: Having a plpgsql function return multiple rows that indicate its progress in a cursor like fashion

Hi dim,

> Would returning a refcursor then using fetch in the application be
> another solution?

I assume not, since nobody stepped forward and offered a way, even
though I suggested that returning a refcursor may be the way to go
(you'll recall that you suggested that to me in IRC - I'm sternocera
there. I believe we met in Paris too). As I said, "I guess my question
boils down to: can I return a cursor, but not to return the result of
a single select, but of multiple different selects in succession, to
report progress as described, or, alternatively, do something that
will produce similar results?"

I also said:

"Cursors simply address the problem of "impedance mismatch"...You
don't have to fetch the result set all at once where that is
impractical. However, the entire result set is available on the server
from the first fetch."

Tom contradicted this, but I believe he just meant that my statement
was technically inaccurate, and not that it was conceptually
inaccurate. My (perhaps incorrect) understanding is that once you open
a cursor, you cannot change that which will later be fetched from the
same cursor - What rows will be returned when everything is fetched is
determined when the cursor is opened. Also, I cannot very well open a
cursor twice, because, as the docs say, "the cursor cannot be open
already" when opening a cursor. So, unless I'm mistaken, I don't think
returning a refcursor helps me here.

> As far as hacking is concerned, I think it boils down to materialise
> against value-per-call implementation, right? Not saying it's easy to
> implement value-per-call support in plpgsql, but should the OP think
> about what's involved, is that the track to follow?
>
>  http://git.postgresql.org/gitweb?p=postgresql.git;a=blob;f=src/backend/utils/fmgr/README;hb=HEAD#l380
>  http://git.postgresql.org/gitweb?p=postgresql.git;a=commitdiff;h=d9a319525591bc437e4770b4e796a7517844a784
>
> The first link is the fmgr/README explaining the concepts, and the
> second one is a recent enough patch dealing with materialise and
> value-per-call in the context of SQL functions.

I'll investigate. To be perfectly frank, I am probably overextending
myself in doing so, because a) I am not familiar with the PostgreSQL
codebase and b) Tom's admonition about the likely difficulty of doing
this indicates that it is probably quite an involved task.

I think it would be useful to articulate, in broad strokes, what this
feature should look like, if not for my benefit, then for the benefit
of whoever will eventually implement it (because, given the
aspirations and momentum of the postgres community, and the obvious
utility of what I've described, I think it's inevitable that *someone*
will).

Regards,
Peter Geoghegan