Re: 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