WITH HOLD ref cursors for plpgsql

[Daniel Wood]

I believe that WITH HOLD cursors was once on a TODO list for plpgsql
along with scrollable cursors. When scrollable cursors was implemented,
but not WITH HOLD, it was dropped from the TODO list.
Robert Haas mentioned in:

http://www.postgresql.org/message-id/AANLkTikjVF6bENfxv-et75g-vHnRyy0y0B+***@mail.gmail.com

that "...it's not really clear what WITH HOLD would do for you." It
makes some sense given that procedures don't support commit/rollback.
However, you might want to return a ref cursor and it certainly might be
useful to have as a with hold cursor.

It seems that independent of the issue of support for transactions in
plpgsql we should support with hold cursors.

[Tom Lane]

Post by Daniel Wood
I believe that WITH HOLD cursors was once on a TODO list for plpgsql
along with scrollable cursors. When scrollable cursors was implemented,
but not WITH HOLD, it was dropped from the TODO list.

Huh? It's been done for years. Unless there's some specific capability
you find to be missing?

The thread you referenced is specifically about plpgsql, not cursors
in general.

regards, tom lane

[Daniel Wood]

Post by Tom Lane

Post by Daniel Wood
I believe that WITH HOLD cursors was once on a TODO list for plpgsql
along with scrollable cursors. When scrollable cursors was implemented,
but not WITH HOLD, it was dropped from the TODO list.

Huh? It's been done for years. Unless there's some specific capability
you find to be missing?
The thread you referenced is specifically about plpgsql, not cursors
in general.
regards, tom lane

So how does one create a "WITH HOLD ref cursor for plpgsql"?

The SQL grammar for "declare" allows it but I can't find it in the
plpgsql grammar for declare.

The link I previously quoted for Pavel/Robert discussions was garbled.
It is:
http://www.postgresql.org/message-id/AANLkTikjVF6bENfxv-et75g-vHnRyy0y0B+***@mail.gmail.com

- Dan

[Tom Lane]

Post by Daniel Wood
So how does one create a "WITH HOLD ref cursor for plpgsql"?

You don't --- as the previous thread mentioned, there doesn't seem
to be a lot of use-case for that, and it goes against plpgsql's
model of cursors as essentially being local variables of functions.

If you're really feeling desperate to use plpgsql to create a cursor
that will be used later by some calling application, you could probably
do EXECUTE 'DECLARE c CURSOR WITH HOLD FOR SELECT ...'. But it's not
immediately clear to me why that's better than letting the calling
application do the DECLARE for itself.

regards, tom lane