Re: Fetch from refcursor and transactions

On Friday, December 23, 2011 5:13:45 am aasat wrote:
> Hi,
>
> I have question about fetching data from refcursor, why this operation
> required to be inside transaction?
>
> for example
>
> with transaction
>
> begin;
> select * from foo();
> fetch all from "<unnamed portal 1>";
> commit;
>
> when I try this without transaction I get error
>
> select * from foo();
> fetch all from "<unnamed portal 1>";
>
> ERROR: cursor "<unnamed portal 1>" does not exist
>
> why cursor "<unnamed portal 1>" are not defined in session and required
> transaction?

http://www.postgresql.org/docs/9.0/interactive/plpgsql-cursors.html#PLPGSQL-CURSOR-USING

39.7.3. Using Cursors

Once a cursor has been opened, it can be manipulated with the statements described here.

These manipulations need not occur in the same function that opened the cursor to begin with.
You can return a refcursor value out of a function and let the caller operate on the cursor. (
Internally, a refcursor value is simply the string name of a so-called portal containing the active query for the cursor. This name can be passed around, assigned to other refcursor variables, and so
on, without disturbing the portal.)

All portals are implicitly closed at transaction end. Therefore a refcursor value is usable to
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
reference an open cursor only until the end of the transaction.
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

>
>
> best regards,
> Wojciech
>
>
>
> --
> View this message in context:
> http://postgresql.1045698.n5.nabble.com/Fetch-from-refcursor-and-transacti
> ons-tp5097158p5097158.html Sent from the PostgreSQL - general mailing list
> archive at Nabble.com.

--
Adrian Klaver
adrian(dot)klaver(at)gmail(dot)com