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);
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
Attachments:
plpgsql-for-over-unbound-cursor.patchtext/x-patch; charset=US-ASCII; name=plpgsql-for-over-unbound-cursor.patchDownload+133-12
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, passedThe 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
Show quoted text
regards, tom lane