Thread: WITH HOLD ref cursors for plpgsql
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+og6BprhgQ@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.
Daniel Wood <dwood@salesforce.com> writes:
> 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
On 02/19/2013 01:10 AM, Tom Lane wrote: > Daniel Wood <dwood@salesforce.com> writes: >> 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+og6BprhgQ@mail.gmail.com - Dan
Daniel Wood <dwood@salesforce.com> writes:
> 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