Thread: FETCH in subqueries or CTEs
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;
--
Craig Ringer
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 > -- > Craig Ringer > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general
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 > > ... 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
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