Re: cursors as table sources

Michael Fuhr <mike@fuhr.org> writes:
> ... However, you could
> write a set-returning function that takes a refcursor argument and
> iterates through the cursor, returning each row, and use that
> function in the FROM clause.  Whether that's a good idea or not is
> something I haven't given much thought to.

From a performance point of view, at least, it's practically guaranteed
not to be a good idea.  By hiding part of the query from the optimizer,
the SRF would prevent any meaningful join optimization from happening.
This applies whether the SRF is reading a cursor or just executing the
query directly.

For the same reason, I can't get very excited about the idea of allowing
a cursor reference in FROM.  The cursor is already planned and there
would be no opportunity to alter its plan based on the join context.

I have some recollection that the cursor-in-FROM idea has come up before
and was shot down on yet other grounds than that.  Try searching the
archives ...

            regards, tom lane