Support plpgsql multi-range in conditional control

Hi

čt 19. 1. 2023 v 10:23 odesílatel 2903807914@qq.com <2903807914@qq.com>
napsal:

Dear hackers, my good friend Hou Jiaxing and I have implemented a version
of the code that supports multiple integer range conditions in the in
condition control of the for loop statement in the plpgsql procedural
language. A typical example is as follows:

postgres=# do $$
declare
i int := 10;
begin
for i in 1..10 by 3, reverse i+10..i+1 by 3 loop
raise info '%', i;
end loop;
end $$;
INFO: 1
INFO: 4
INFO: 7
INFO: 10
INFO: 20
INFO: 17
INFO: 14
INFO: 11
do
postgres=#

Hope to get your feedback, thank you!

I don't like it. The original design of ADA language is to be a safe and
simple language. Proposed design is in 100% inversion.

What use case it should to support?

Regards

Pavel

Hello, thank you very much for your reply. But I think you may have misunderstood what we have done.

What we do this time is that we can use multiple range ranges (condition_iterator) after in. Previously, we can only use such an interval [lower, upper] after in, but in some scenarios, we may need a list: condition_ iterator[,condition_iterator ...]

condition_iterator:
[ REVERSE ] expression .. expression [ BY expression ]

Thanks again!

songjinzhou (2903807914@qq.com)

From: Pavel Stehule
Date: 2023-01-19 21:04
To: 2903807914@qq.com
CC: pgsql-hackers; 1276576182
Subject: Re: Support plpgsql multi-range in conditional control
Hi

čt 19. 1. 2023 v 10:23 odesílatel 2903807914@qq.com <2903807914@qq.com> napsal:
Dear hackers, my good friend Hou Jiaxing and I have implemented a version of the code that supports multiple integer range conditions in the in condition control of the for loop statement in the plpgsql procedural language. A typical example is as follows:

postgres=# do $$
declare
i int := 10;
begin
for i in 1..10 by 3, reverse i+10..i+1 by 3 loop
raise info '%', i;
end loop;
end $$;
INFO: 1
INFO: 4
INFO: 7
INFO: 10
INFO: 20
INFO: 17
INFO: 14
INFO: 11
do
postgres=#

Hope to get your feedback, thank you!

I don't like it. The original design of ADA language is to be a safe and simple language. Proposed design is in 100% inversion.

What use case it should to support?

Regards

Pavel

2903807914@qq.com

Pavel Stehule <pavel.stehule@gmail.com> writes:

čt 19. 1. 2023 v 10:23 odesílatel 2903807914@qq.com <2903807914@qq.com>
napsal:

Dear hackers, my good friend Hou Jiaxing and I have implemented a version
of the code that supports multiple integer range conditions in the in
condition control of the for loop statement in the plpgsql procedural
language. A typical example is as follows:

I don't like it. The original design of ADA language is to be a safe and
simple language. Proposed design is in 100% inversion.

Yeah, I'm pretty dubious about this too. plpgsql's FOR-loop syntax is
already badly overloaded, to the point where it's hard to separate
the true intent of a statement. We have very ad-hoc rules in there
like "if the first thing after IN is a var of type refcursor, then
it's FOR-IN-cursor, otherwise it couldn't possibly be that". (So
much for functions returning refcursor, for example.) Similarly the
"FOR x IN m..n" syntax has a shaky assumption that ".." couldn't
possibly appear in mainline SQL. If you make any sort of syntax
error you're likely to get a very unintelligible complaint --- or
worse, it might take it and do something you did not expect.

I fear that allowing more complexity in "FOR x IN m..n" will make
those problems even worse. The proposed patch gives comma a special
status akin to ".."'s, but comma definitely *can* appear within SQL
expressions --- admittedly, it should only appear within parentheses,
but now you're reliant on the user keeping their parenthesization
straight in order to avoid going off into the weeds. I think this
change increases the chances of confusion with FOR-IN-SELECT as well.

If there were a compelling use-case for what you suggest then
maybe it'd be worth accepting those risks. But I share Pavel's
opinion that there's little use-case. We've not heard a request
for such a feature before, AFAIR.

regards, tom lane

čt 19. 1. 2023 v 15:20 odesílatel 2903807914@qq.com <2903807914@qq.com>
napsal:

Hello, thank you very much for your reply. But I think you may have
misunderstood what we have done.

What we do this time is that we can use multiple range ranges
(condition_iterator) after in. Previously, we can only use such an interval
[lower, upper] after in, but in some scenarios, we may need a list: *condition_
iterator[,condition_iterator ...]*

condition_iterator:
[ REVERSE ] expression .. expression [ BY expression ]

then you can use second outer for over an array or just while cycle

Reards

Pavel

čt 19. 1. 2023 v 16:54 odesílatel Pavel Stehule <pavel.stehule@gmail.com>
napsal:

čt 19. 1. 2023 v 15:20 odesílatel 2903807914@qq.com <2903807914@qq.com>
napsal:

Hello, thank you very much for your reply. But I think you may have
misunderstood what we have done.

What we do this time is that we can use multiple range ranges
(condition_iterator) after in. Previously, we can only use such an interval
[lower, upper] after in, but in some scenarios, we may need a list: *condition_
iterator[,condition_iterator ...]*

condition_iterator:
[ REVERSE ] expression .. expression [ BY expression ]

then you can use second outer for over an array or just while cycle

I wrote simple example:

create type range_expr as (r int4range, s int);

do
$$
declare re range_expr;
begin
foreach re in array ARRAY[('[10, 20]', 1), ('[100, 200]', 10)]
loop
for i in lower(re.r) .. upper(re.r) by re.s
loop
raise notice '%', i;
end loop;
end loop;
end;
$$;

But just I don't know what is wrong on

begin
for i in 10..20
loop
raise notice '%', i;
end loop;

for i in 100 .. 200 by 10
loop
raise notice '%', i;
end loop;
end;

and if there are some longer bodies you should use function or procedure.
Any different cycle is separated. PLpgSQL (like PL/SQL or ADA) are verbose
languages. There is no goal to have short, heavy code.

Regards

Pavel

Hello, Pavel Stehule:

Thank you very much for your verification. The test cases you provided work well here:

For your second example, we can easily merge, as follows:

For scenarios that can be merged, we can choose to use this function to reduce code redundancy; If the operations performed in the loop are different, you can still select the previous use method, as follows:

In response to Tom's question about cursor and the case of in select: I don't actually allow such syntax here. The goal is simple: we only expand the range of integers after in, and other cases remain the same.
Thank you again for your ideas. Such a discussion is very meaningful!

songjinzhou(2903807914@qq.com)

From: Pavel Stehule
Date: 2023-01-20 00:17
To: 2903807914@qq.com
CC: pgsql-hackers
Subject: Re: Re: Support plpgsql multi-range in conditional control

čt 19. 1. 2023 v 16:54 odesílatel Pavel Stehule <pavel.stehule@gmail.com> napsal:

čt 19. 1. 2023 v 15:20 odesílatel 2903807914@qq.com <2903807914@qq.com> napsal:
Hello, thank you very much for your reply. But I think you may have misunderstood what we have done.

What we do this time is that we can use multiple range ranges (condition_iterator) after in. Previously, we can only use such an interval [lower, upper] after in, but in some scenarios, we may need a list: condition_ iterator[,condition_iterator ...]

condition_iterator:
[ REVERSE ] expression .. expression [ BY expression ]

then you can use second outer for over an array or just while cycle

I wrote simple example:

create type range_expr as (r int4range, s int);

do
$$
declare re range_expr;
begin
foreach re in array ARRAY[('[10, 20]', 1), ('[100, 200]', 10)]
loop
for i in lower(re.r) .. upper(re.r) by re.s
loop
raise notice '%', i;
end loop;
end loop;
end;
$$;

But just I don't know what is wrong on

begin
for i in 10..20
loop
raise notice '%', i;
end loop;

for i in 100 .. 200 by 10
loop
raise notice '%', i;
end loop;
end;

and if there are some longer bodies you should use function or procedure. Any different cycle is separated. PLpgSQL (like PL/SQL or ADA) are verbose languages. There is no goal to have short, heavy code.

Regards

Pavel

Hi

pá 20. 1. 2023 v 4:25 odesílatel songjinzhou <2903807914@qq.com> napsal:

Hello, Pavel Stehule:

Thank you very much for your verification. The test cases you provided
work well here:

For your second example, we can easily merge, as follows:

For scenarios that can be merged, we can choose to use this function to
reduce code redundancy; If the operations performed in the loop are
different, you can still select the previous use method, as follows:

In response to Tom's question about cursor and the case of in select: I
don't actually allow such syntax here. The goal is simple: we only expand
the range of integers after in, and other cases remain the same.
Thank you again for your ideas. Such a discussion is very meaningful!

------------------------------
songjinzhou(2903807914@qq.com)

*From:* Pavel Stehule <pavel.stehule@gmail.com>
*Date:* 2023-01-20 00:17
*To:* 2903807914@qq.com
*CC:* pgsql-hackers <pgsql-hackers@lists.postgresql.org>
*Subject:* Re: Re: Support plpgsql multi-range in conditional control

čt 19. 1. 2023 v 16:54 odesílatel Pavel Stehule <pavel.stehule@gmail.com>
napsal:

čt 19. 1. 2023 v 15:20 odesílatel 2903807914@qq.com <2903807914@qq.com>
napsal:

Hello, thank you very much for your reply. But I think you may have
misunderstood what we have done.

What we do this time is that we can use multiple range ranges
(condition_iterator) after in. Previously, we can only use such an interval
[lower, upper] after in, but in some scenarios, we may need a list: *condition_
iterator[,condition_iterator ...]*

condition_iterator:
[ REVERSE ] expression .. expression [ BY expression ]

then you can use second outer for over an array or just while cycle

I wrote simple example:

create type range_expr as (r int4range, s int);

do
$$
declare re range_expr;
begin
foreach re in array ARRAY[('[10, 20]', 1), ('[100, 200]', 10)]
loop
for i in lower(re.r) .. upper(re.r) by re.s
loop
raise notice '%', i;
end loop;
end loop;
end;
$$;

But just I don't know what is wrong on

begin
for i in 10..20
loop
raise notice '%', i;
end loop;

for i in 100 .. 200 by 10
loop
raise notice '%', i;
end loop;
end;

and if there are some longer bodies you should use function or procedure.
Any different cycle is separated. PLpgSQL (like PL/SQL or ADA) are verbose
languages. There is no goal to have short, heavy code.

Regards

Pavel

Maybe you didn't understand my reply. Without some significant real use
case, I am strongly against the proposed feature and merging your patch to
upstream. I don't see any reason to enhance language with this feature.

Regards

Pavel

Hello, this usage scenario is from Oracle's PL/SQL language (I have been doing the function development of PL/SQL language for some time). I think this patch is very practical and will expand our for loop scenario. In short, I look forward to your reply.

Happy Chinese New Year!

songjinzhou(2903807914@qq.com)

Maybe you didn't understand my reply. Without some significant real use case, I am strongly against the proposed feature and merging your patch to upstream. I don't see any reason to enhance language with this feature.

Regards

Pavel

Hi

st 25. 1. 2023 v 15:18 odesílatel songjinzhou <2903807914@qq.com> napsal:

Hello, this usage scenario is from Oracle's PL/SQL language (I have been
doing the function development of PL/SQL language for some time). I think
this patch is very practical and will expand our for loop scenario. In
short, I look forward to your

I don't see any real usage. PL/SQL doesn't support proposed syntax.

Regards

Pavel

Hello, this is the target I refer to. At present, our patch supports this usage, so I later thought of developing this patch.

songjinzhou(2903807914@qq.com)

From: Pavel Stehule
Date: 2023-01-25 22:21
To: songjinzhou
CC: pgsql-hackers
Subject: Re: Re: Support plpgsql multi-range in conditional control
Hi

st 25. 1. 2023 v 15:18 odesílatel songjinzhou <2903807914@qq.com> napsal:
Hello, this usage scenario is from Oracle's PL/SQL language (I have been doing the function development of PL/SQL language for some time). I think this patch is very practical and will expand our for loop scenario. In short, I look forward to your

I don't see any real usage. PL/SQL doesn't support proposed syntax.

Regards

Pavel

reply.

Happy Chinese New Year!

songjinzhou(2903807914@qq.com)

Maybe you didn't understand my reply. Without some significant real use case, I am strongly against the proposed feature and merging your patch to upstream. I don't see any reason to enhance language with this feature.

Regards

Pavel

Hi

st 25. 1. 2023 v 15:39 odesílatel songjinzhou <2903807914@qq.com> napsal:

Hello, this is the target I refer to. At present, our patch supports this
usage, so I later thought of developing this patch.

------------------------------
songjinzhou(2903807914@qq.com)

*From:* Pavel Stehule <pavel.stehule@gmail.com>
*Date:* 2023-01-25 22:21
*To:* songjinzhou <2903807914@qq.com>
*CC:* pgsql-hackers <pgsql-hackers@lists.postgresql.org>
*Subject:* Re: Re: Support plpgsql multi-range in conditional control
Hi

ok, I was wrong, PL/SQL supports this syntax. But what is the real use
case? This is an example from the book.

Regards

Pavel

Hello, my personal understanding is that you can use multiple iterative controls (as a merge) in a fo loop, otherwise we can only separate these iterative controls, but in fact, they may do the same thing.

songjinzhou(2903807914@qq.com)

From: Pavel Stehule
Date: 2023-01-25 23:24
To: songjinzhou
CC: pgsql-hackers
Subject: Re: Re: Support plpgsql multi-range in conditional control
Hi

st 25. 1. 2023 v 15:39 odesílatel songjinzhou <2903807914@qq.com> napsal:
Hello, this is the target I refer to. At present, our patch supports this usage, so I later thought of developing this patch.

songjinzhou(2903807914@qq.com)

From: Pavel Stehule
Date: 2023-01-25 22:21
To: songjinzhou
CC: pgsql-hackers
Subject: Re: Re: Support plpgsql multi-range in conditional control
Hi

ok, I was wrong, PL/SQL supports this syntax. But what is the real use case? This is an example from the book.

Regards

Pavel

st 25. 1. 2023 v 15:18 odesílatel songjinzhou <2903807914@qq.com> napsal:
Hello, this usage scenario is from Oracle's PL/SQL language (I have been doing the function development of PL/SQL language for some time). I think this patch is very practical and will expand our for loop scenario. In short, I look forward to your

I don't see any real usage. PL/SQL doesn't support proposed syntax.

Regards

Pavel

reply.

Happy Chinese New Year!

songjinzhou(2903807914@qq.com)

Maybe you didn't understand my reply. Without some significant real use case, I am strongly against the proposed feature and merging your patch to upstream. I don't see any reason to enhance language with this feature.

Regards

Pavel

Hi

st 25. 1. 2023 v 16:39 odesílatel songjinzhou <2903807914@qq.com> napsal:

Hello, my personal understanding is that you can use multiple iterative
controls (as a merge) in a fo loop, otherwise we can only separate these
iterative controls, but in fact, they may do the same thing.

1. please, don't use top posting in this mailing list
https://en.wikipedia.org/wiki/Posting_styl

2. I understand the functionality, but I don't think there is a real
necessity to support this functionality. Not in this static form, and just
for integer type.

Postgres has a nice generic type "multirange". I can imagine some iterator
over the value of multirange, but I cannot imagine the necessity of a
richer iterator over just integer range. So the question is, what is the
real possible use case of this proposed functionality?

Regards

Pavel

Hi

st 25. 1. 2023 v 16:39 odesílatel songjinzhou <2903807914@qq.com> napsal: Hello, my personal understanding is that you can use multiple iterative controls (as a merge) in a fo loop, otherwise we can only separate these iterative controls, but in fact, they may do the same thing.

1. please, don't use top posting in this mailing list https://en.wikipedia.org/wiki/Posting_styl

2. I understand the functionality, but I don't think there is a real necessity to support this functionality. Not in this static form, and just for integer type.

Postgres has a nice generic type "multirange". I can imagine some iterator over the value of multirange, but I cannot imagine the necessity of a richer iterator over just integer range. So the question is, what is the real possible use case of this proposed functionality?

1. I'm very sorry that my personal negligence has caused obstacles to your reading. Thank you for your reminding.
2. With regard to the use of this function, my understanding is relatively simple: there are many for loops that may do the same things. We can reduce our sql redundancy by merging iterative control; It is also more convenient to understand and read logically.

As follows, we can only repeat the for statement before we use such SQL:

begin
for i in 10..20 loop
raise notice '%', i; -- Things to do
end loop;

for i in 100 .. 200 by 10 loop
raise notice '%', i; -- Things to do
end loop;
end;

But now we can simplify it as follows:

begin
for i in 10..20, 100 .. 200 by 10 loop
raise notice '%', i; -- Things to do
end loop;
end;

Although we can only use integer iterative control here, this is just a horizontal expansion of the previous logic. Thank you very much for your reply. I am very grateful!

---

songjinzhou(2903807914@qq.com)

st 25. 1. 2023 v 17:22 odesílatel songjinzhou <2903807914@qq.com> napsal:

Hi

st 25. 1. 2023 v 16:39 odesílatel songjinzhou <2903807914@qq.com>

napsal: Hello, my personal understanding is that you can use multiple
iterative controls (as a merge) in a fo loop, otherwise we can only
separate these iterative controls, but in fact, they may do the same thing.

1. please, don't use top posting in this mailing list

https://en.wikipedia.org/wiki/Posting_styl

2. I understand the functionality, but I don't think there is a real

necessity to support this functionality. Not in this static form, and just
for integer type.

Postgres has a nice generic type "multirange". I can imagine some

iterator over the value of multirange, but I cannot imagine the necessity
of a richer iterator over just integer range. So the question is, what is
the real possible use case of this proposed functionality?

1. I'm very sorry that my personal negligence has caused obstacles to your
reading. Thank you for your reminding.
2. With regard to the use of this function, my understanding is relatively
simple: there are many for loops that may do the same things. We can reduce
our sql redundancy by merging iterative control; It is also more convenient
to understand and read logically.

As follows, we can only repeat the for statement before we use such SQL:

begin
for i in 10..20 loop
raise notice '%', i; -- Things to do
end loop;

for i in 100 .. 200 by 10 loop
raise notice '%', i; -- Things to do
end loop;
end;

But now we can simplify it as follows:

begin
for i in 10..20, 100 .. 200 by 10 loop
raise notice '%', i; -- Things to do
end loop;
end;

Although we can only use integer iterative control here, this is just a
horizontal expansion of the previous logic. Thank you very much for your
reply. I am very grateful!

Unfortunately, this is not a real use case - this is not an example from
the real world.

Regards

Pavel

On Wed, 25 Jan 2023 at 12:02, Pavel Stehule <pavel.stehule@gmail.com> wrote:

st 25. 1. 2023 v 17:22 odesílatel songjinzhou <2903807914@qq.com> napsal:

As follows, we can only repeat the for statement before we use such SQL:

begin
for i in 10..20 loop
raise notice '%', i; -- Things to do
end loop;

for i in 100 .. 200 by 10 loop
raise notice '%', i; -- Things to do
end loop;
end;

But now we can simplify it as follows:

begin
for i in 10..20, 100 .. 200 by 10 loop
raise notice '%', i; -- Things to do
end loop;
end;

Although we can only use integer iterative control here, this is just a
horizontal expansion of the previous logic. Thank you very much for your
reply. I am very grateful!

Unfortunately, this is not a real use case - this is not an example from
the real world.

And anyway, this is already supported using generate_series() and UNION:

odyssey=> do $$ declare i int; begin for i in select generate_series (10,
20) union all select generate_series (100, 200, 10) do loop raise notice
'i=%', i; end loop; end;$$;
NOTICE: i=10
NOTICE: i=11
NOTICE: i=12
NOTICE: i=13
NOTICE: i=14
NOTICE: i=15
NOTICE: i=16
NOTICE: i=17
NOTICE: i=18
NOTICE: i=19
NOTICE: i=20
NOTICE: i=100
NOTICE: i=110
NOTICE: i=120
NOTICE: i=130
NOTICE: i=140
NOTICE: i=150
NOTICE: i=160
NOTICE: i=170
NOTICE: i=180
NOTICE: i=190
NOTICE: i=200
DO
odyssey=>

The existing x..y notation is just syntactic sugar for a presumably common
case (although I’m dubious how often one really loops through a range of
numbers — surely in a database looping through a query result is
overwhelmingly dominant?); I don’t think you’ll find much support around
here for adding more syntax possibilities to the loop construct.