Обсуждение: proposal - plpgsql - FOR over unbound cursor

Поиск
Список
Период
Сортировка

proposal - plpgsql - FOR over unbound cursor

От
Pavel Stehule
Дата:
Hi

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. 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.

Implementation is simple, usage is simple too:

CREATE OR REPLACE FUNCTION public.forc02()
 RETURNS void
 LANGUAGE plpgsql
AS $function$
declare
  c refcursor;
  r record;
begin
  open c for select * from generate_series(1,20) g(v);

  for r in c
  loop
    raise notice 'cycle body one %', r.v;
    exit when r.v >= 6;
  end loop;

  for r in c
  loop
    raise notice 'cycle body two %', r.v;
  end loop;

   close c;
end
$function$

Comments, notes?

Regards

Pavel

Вложения

Re: proposal - plpgsql - FOR over unbound cursor

От
Asif Rehman
Дата:
The following review has been posted through the commitfest application:
make installcheck-world:  tested, passed
Implements feature:       tested, passed
Spec compliant:           not tested
Documentation:            tested, passed

The patch applies cleanly and AFAICS there are no issues with the patch.

The new status of this patch is: Ready for Committer

Re: proposal - plpgsql - FOR over unbound cursor

От
Pavel Stehule
Дата:


po 8. 6. 2020 v 13:40 odesílatel Asif Rehman <asifr.rehman@gmail.com> napsal:
The following review has been posted through the commitfest application:
make installcheck-world:  tested, passed
Implements feature:       tested, passed
Spec compliant:           not tested
Documentation:            tested, passed

The patch applies cleanly and AFAICS there are no issues with the patch.

The new status of this patch is: Ready for Committer

Thank you

Pavel

Re: proposal - plpgsql - FOR over unbound cursor

От
Tom Lane
Дата:
Pavel Stehule <pavel.stehule@gmail.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.

> 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.

            regards, tom lane



Re: proposal - plpgsql - FOR over unbound cursor

От
Pavel Stehule
Дата:


st 1. 7. 2020 v 20:06 odesílatel Tom Lane <tgl@sss.pgh.pa.us> napsal:
Pavel Stehule <pavel.stehule@gmail.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