FETCH in subqueries or CTEs

On Fri, 2012-08-24 at 09:35 +0800, Craig Ringer wrote:

Hi all

I've noticed that FETCH doesn't seem to be supported in subqueries or in
CTEs.

Is there a specific reason for that, beyond "nobody's needed it and
implemented it"? I'm not complaining at all, merely curious.

1. Cursors have their own snapshot, so it would be kind of like looking
at two snapshots of data at the same time. That would be a little
strange.

2. For regular subqueries, it would also be potentially
non-deterministic, because the FETCH operation has the side effect of
advancing the cursor. So, if you had something like "SELECT * FROM
(FETCH 1 FROM mycursor) x WHERE FALSE", it's not clear whether the FETCH
would execute or not. After the query, it may have advanced the cursor
or may not have, depending on whether the optimizer decided it didn't
need to compute the subquery.

3. Cursors are really meant for a more effective interaction with the
client, it's not really meant as an operator (and it doesn't change the
results, anyway). You can already do LIMIT/OFFSET in a subquery if you
need that kind of thing.

All that being said, there may be some use case for something like what
you are describing, if you get creative.

Regards,
Jeff Davis

Hello

2012/8/24 Craig Ringer <ringerc@ringerc.id.au>:

Hi all

I've noticed that FETCH doesn't seem to be supported in subqueries or in
CTEs.

Is there a specific reason for that, beyond "nobody's needed it and
implemented it"? I'm not complaining at all, merely curious.

A toy example:

DECLARE somecursor CURSOR FOR SELECT generate_series(1,1000)
SELECT * FROM ( FETCH ALL FROM somecursor ) x;

produces:

ERROR: syntax error at or near "FETCH"
LINE 1: SELECT * FROM ( FETCH ALL FROM somecursor ) x;
^
Same with a CTE:

WITH x AS ( FETCH ALL FROM somecursor ) SELECT * FROM x;

ERROR: syntax error at or near "FETCH"
LINE 1: WITH x AS ( FETCH ALL FROM somecursor ) SELECT * FROM x;

you can't mix planned and unplanned statements together - think about
stored plans every time

Regards

Pavel

On 08/24/2012 12:34 PM, Pavel Stehule wrote:

you can't mix planned and unplanned statements together - think about
stored plans every time

Thanks Pavel and Jeff.

I can't say I fully understand the arguments, but I'll take it that
accepting cursors in CTEs or subqueries wouldn't make sense. I guess the
main issue really is that you'd have to materialize them anyway to avoid
issues with multiple scans, so there's little point having a cursor.

I didn't find a reasonable way to simply fetch a cursor into a (possibly
temporary) table, like:

INSERT INTO sometable FETCH ALL FROM somecursor;

... which could be handy with PL/PgSQL functions that return multiple
refcursors. It only seems to be possible via a PL/PgSQL wrapper that
loops over the cursor and returns a rowset.

--
Craig Ringer

2012/8/24 Craig Ringer <ringerc@ringerc.id.au>:

On 08/24/2012 12:34 PM, Pavel Stehule wrote:

you can't mix planned and unplanned statements together - think about
stored plans every time

Thanks Pavel and Jeff.

I can't say I fully understand the arguments, but I'll take it that
accepting cursors in CTEs or subqueries wouldn't make sense. I guess the
main issue really is that you'd have to materialize them anyway to avoid
issues with multiple scans, so there's little point having a cursor.

I didn't find a reasonable way to simply fetch a cursor into a (possibly
temporary) table, like:

INSERT INTO sometable FETCH ALL FROM somecursor;

it should be implemented as function - like materialize_cursor(cursor, table)

I would to see full support of stored procedures (with multirecordsets) rather.

Regards

Pavel

Craig Ringer <ringerc@ringerc.id.au> writes:

I didn't find a reasonable way to simply fetch a cursor into a (possibly
temporary) table, like:
INSERT INTO sometable FETCH ALL FROM somecursor;

Why would you bother with a cursor, and not just INSERT ... SELECT
using the original query?

Putting a cursor in between will just make matters more complicated and
slower. (For one thing, the plan created for a cursor is optimized for
incremental fetching not read-it-all-at-once.)

regards, tom lane

On 08/24/2012 10:31 PM, Tom Lane wrote:

Craig Ringer <ringerc@ringerc.id.au> writes:

I didn't find a reasonable way to simply fetch a cursor into a (possibly
temporary) table, like:
INSERT INTO sometable FETCH ALL FROM somecursor;

Why would you bother with a cursor, and not just INSERT ... SELECT
using the original query?

I wouldn't. The context - and the reason it's mostly a matter of
curiosity, not something I care about - is that it arose out of
discussion elsewhere on how to work with pre-defined PL/PgSQL functions
that return multiple refcursors.

--
Craig Ringer