Обсуждение: proposal - plpgsql - FOR over unbound cursor
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);
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;
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$
$function$
Comments, notes?
Regards
Pavel
Вложения
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
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
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
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