Re: DECLARE CURSOR must not contain data-modifying statements in WITH

On Wed, Sep 21, 2011 at 12:19 PM, Andres Freund <andres@anarazel.de> wrote:
>        /*
>         * We also disallow data-modifying WITH in a cursor.  (This could be
>         * allowed, but the semantics of when the updates occur might be
>         * surprising.)
>         */
>        if (result->hasModifyingCTE)
>                ereport(ERROR,
>                                (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
>                                 errmsg("DECLARE CURSOR must not contain data-modifying
> statements in WITH")));
>
> Given that cursors are about the only sensible way to return larger amounts of
> data, that behaviour reduces the usefulness of wCTEs a bit.
>
> Whats the exact cause of concern here? I personally don't think there is a
> problem documenting that you should fetch the cursor fully before relying on
> the updated tables to be in a sensible state. But that may be just me.

Well, it looks like right now you can't even using a simple INSERT ..
RETURNING there:

rhaas=# create table wuzzle (a int);
CREATE TABLE
rhaas=# declare w cursor for insert into wuzzle select g from
generate_series(1, 10) g returning g;
ERROR:  syntax error at or near "insert"
LINE 1: declare w cursor for insert into wuzzle select g from genera...                            ^

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company