Re: proposal - plpgsql - FOR over unbound cursor

st 1. 7. 2020 v 20:06 odesílatel Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> napsal:

> Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> writes:
> > Last week I played with dbms_sql extension and some patterns of usage
> > cursor in PL/SQL and PL/pgSQL. I found fact, so iteration over cursor
> (FOR
> > statement) doesn't support unbound cursors. I think so this limit is not
> > necessary.
>
> I guess I don't understand why we should add this. What does it do
> that can't be done better with a plain FOR-over-SELECT?
>
> The example you give of splitting an iteration into two loops doesn't
> inspire me to think it's useful; it looks more like encouraging awful
> programming practice.
>

There are few points for this feature.

1. possibility to use FOR cycle for refcursors. Refcursor can be passed as
argument and it can be practical for some workflows with multiple steps -
preparing, iterations, closing.

2. symmetry - FETCH statement can be used for bound/unbound cursors. FOR
cycle can be used only for bound cursors.

3. It is one pattern (and I have not an idea how often) used by the dms_sql
package. You can get a refcursor as a result of some procedures, and next
steps you can iterate over this cursor. PL/SQL can use FOR cycle (and it is
not possible in PL/pgSQL).

> > This statement can open portal for bound cursor or can iterate
> > over before opened portal. When portal was opened inside FOR statement,
> > then it is closed inside this statement.
>
> And this definition seems quite inconsistent and error-prone.
> The point of a FOR loop, IMO, is to have a fairly self-contained
> definition of the set of iterations that will occur. This
> eliminates that property, leaving you with something no cleaner
> than a hand-built loop around a FETCH command.
>

This is 100% valid for bound cursors. We don't allow unbound cursors there
now, and we can define behaviour.

I understand that this feature increases the complexity of FOR cycle, but I
see an interesting possibility to create a dynamic cursor somewhere and
iterate elsewhere. My motivation is little bit near to
https://commitfest.postgresql.org/28/2376/

Regards

Pavel

> regards, tom lane
>