Support plpgsql multi-range in conditional control

Started by 2903807914@qq.comalmost 3 years ago17 messages

almost 3 years ago

1 attachment(s)

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!

2903807914@qq.com

Attachments:

0001-Support-plpgsql-multi-range-in-conditional-control.patchapplication/octet-stream; name=0001-Support-plpgsql-multi-range-in-conditional-control.patchDownload

From dd690f7bf2a305768bce59fc5690316b1283bbee Mon Sep 17 00:00:00 2001
From: songjinzhou <2903807914@qq.com>
Date: Wed, 18 Jan 2023 17:49:33 +0800
Subject: [PATCH] Support plpgsql multi-range in conditional control

---
 doc/src/sgml/plpgsql.sgml                     |  15 +-
 .../plpgsql/src/expected/plpgsql_control.out  | 112 +++++++++++
 src/pl/plpgsql/src/pl_exec.c                  | 190 +++++++++---------
 src/pl/plpgsql/src/pl_funcs.c                 |  38 ++--
 src/pl/plpgsql/src/pl_gram.y                  | 123 +++++++++---
 src/pl/plpgsql/src/plpgsql.h                  |  13 +-
 src/pl/plpgsql/src/sql/plpgsql_control.sql    |  57 ++++++
 7 files changed, 406 insertions(+), 142 deletions(-)

diff --git a/doc/src/sgml/plpgsql.sgml b/doc/src/sgml/plpgsql.sgml
index 8897a5450a..edf523593a 100644
--- a/doc/src/sgml/plpgsql.sgml
+++ b/doc/src/sgml/plpgsql.sgml
@@ -2518,7 +2518,7 @@ END LOOP;
 
 <synopsis>
 <optional> &lt;&lt;<replaceable>label</replaceable>&gt;&gt; </optional>
-FOR <replaceable>name</replaceable> IN <optional> REVERSE </optional> <replaceable>expression</replaceable> .. <replaceable>expression</replaceable> <optional> BY <replaceable>expression</replaceable> </optional> LOOP
+FOR <replaceable>name</replaceable> IN <replaceable>condition_iterator</replaceable> <optional>, <replaceable>condition_iterator</replaceable> <optional> ... </optional></optional> LOOP
     <replaceable>statements</replaceable>
 END LOOP <optional> <replaceable>label</replaceable> </optional>;
 </synopsis>
@@ -2529,6 +2529,15 @@ END LOOP <optional> <replaceable>label</replaceable> </optional>;
         <replaceable>name</replaceable> is automatically defined as type
         <type>integer</type> and exists only inside the loop (any existing
         definition of the variable name is ignored within the loop).
+        </para>
+
+<synopsis>
+condition_iterator:
+<optional> REVERSE </optional> <replaceable>expression</replaceable> .. <replaceable>expression</replaceable> <optional> BY <replaceable>expression</replaceable> </optional>
+</synopsis>
+
+       <para>
+        Multiple iteration controls may be chained together by separating them with commas. The composition of each condition_iterator is as follows:
         The two expressions giving
         the lower and upper bound of the range are evaluated once when entering
         the loop. If the <literal>BY</literal> clause isn't specified the iteration
@@ -2552,6 +2561,10 @@ END LOOP;
 FOR i IN REVERSE 10..1 BY 2 LOOP
     -- i will take on the values 10,8,6,4,2 within the loop
 END LOOP;
+
+FOR I IN 1..10 BY 3, REVERSE I+10..I+1 BY 3 LOOP
+    -- i will take on the values 1,4,7,10,20,17,14,11 within the loop
+END LOOP;
 </programlisting>
        </para>
 
diff --git a/src/pl/plpgsql/src/expected/plpgsql_control.out b/src/pl/plpgsql/src/expected/plpgsql_control.out
index 328bd48586..d74602a04a 100644
--- a/src/pl/plpgsql/src/expected/plpgsql_control.out
+++ b/src/pl/plpgsql/src/expected/plpgsql_control.out
@@ -79,6 +79,118 @@ begin
 end$$;
 ERROR:  BY value of FOR loop must be greater than zero
 CONTEXT:  PL/pgSQL function inline_code_block line 3 at FOR with integer loop variable
+-- Test in condition list
+do $$
+declare
+	i int;
+begin
+	for i in 1..3 , 51..55 loop
+		raise notice '%', i;
+	end loop;
+
+  for i in 1..3 , reverse 55..51 loop
+		raise info '%', i;
+	end loop;
+	
+	for i in reverse 1..3 loop
+		raise notice '%', i;
+	end loop;
+	
+	for i in 1..3 loop
+		raise notice '%', i;
+	end loop;
+	
+	for i in reverse 3..1 loop
+		raise notice '%', i;
+	end loop;
+	
+	for i in 1..10 by 3 loop
+		raise notice '1..10 by 3: i = %', i;
+	end loop;
+end$$;
+NOTICE:  1
+NOTICE:  2
+NOTICE:  3
+NOTICE:  51
+NOTICE:  52
+NOTICE:  53
+NOTICE:  54
+NOTICE:  55
+INFO:  1
+INFO:  2
+INFO:  3
+INFO:  55
+INFO:  54
+INFO:  53
+INFO:  52
+INFO:  51
+NOTICE:  1
+NOTICE:  2
+NOTICE:  3
+NOTICE:  3
+NOTICE:  2
+NOTICE:  1
+NOTICE:  1..10 by 3: i = 1
+NOTICE:  1..10 by 3: i = 4
+NOTICE:  1..10 by 3: i = 7
+NOTICE:  1..10 by 3: i = 10
+do $$
+declare
+   i int := 10;
+begin
+   for i in reverse i+10..i+1 loop
+      raise info '%', i;
+   end loop;
+end $$;
+INFO:  20
+INFO:  19
+INFO:  18
+INFO:  17
+INFO:  16
+INFO:  15
+INFO:  14
+INFO:  13
+INFO:  12
+INFO:  11
+do $$
+declare
+   j int := 10;
+begin
+   for i in 1..3, reverse j+10..j+1 loop
+      raise info '%', i;
+   end loop;
+end $$;
+INFO:  1
+INFO:  2
+INFO:  3
+INFO:  20
+INFO:  19
+INFO:  18
+INFO:  17
+INFO:  16
+INFO:  15
+INFO:  14
+INFO:  13
+INFO:  12
+INFO:  11
+do $$
+declare
+   j int := 10;
+begin
+   for i in reverse j+10..j+1 loop
+      raise info '%', i;
+   end loop;
+end $$;
+INFO:  20
+INFO:  19
+INFO:  18
+INFO:  17
+INFO:  16
+INFO:  15
+INFO:  14
+INFO:  13
+INFO:  12
+INFO:  11
 -- CONTINUE statement
 create table conttesttbl(idx serial, v integer);
 insert into conttesttbl(v) values(10);
diff --git a/src/pl/plpgsql/src/pl_exec.c b/src/pl/plpgsql/src/pl_exec.c
index 37da624388..8460ac001b 100644
--- a/src/pl/plpgsql/src/pl_exec.c
+++ b/src/pl/plpgsql/src/pl_exec.c
@@ -2675,48 +2675,19 @@ exec_stmt_fori(PLpgSQL_execstate *estate, PLpgSQL_stmt_fori *stmt)
 	int32		step_value;
 	bool		found = false;
 	int			rc = PLPGSQL_RC_OK;
+	
+	ListCell	*lc;
 
 	var = (PLpgSQL_var *) (estate->datums[stmt->var->dno]);
 
-	/*
-	 * Get the value of the lower bound
-	 */
-	value = exec_eval_expr(estate, stmt->lower,
-						   &isnull, &valtype, &valtypmod);
-	value = exec_cast_value(estate, value, &isnull,
-							valtype, valtypmod,
-							var->datatype->typoid,
-							var->datatype->atttypmod);
-	if (isnull)
-		ereport(ERROR,
-				(errcode(ERRCODE_NULL_VALUE_NOT_ALLOWED),
-				 errmsg("lower bound of FOR loop cannot be null")));
-	loop_value = DatumGetInt32(value);
-	exec_eval_cleanup(estate);
-
-	/*
-	 * Get the value of the upper bound
-	 */
-	value = exec_eval_expr(estate, stmt->upper,
-						   &isnull, &valtype, &valtypmod);
-	value = exec_cast_value(estate, value, &isnull,
-							valtype, valtypmod,
-							var->datatype->typoid,
-							var->datatype->atttypmod);
-	if (isnull)
-		ereport(ERROR,
-				(errcode(ERRCODE_NULL_VALUE_NOT_ALLOWED),
-				 errmsg("upper bound of FOR loop cannot be null")));
-	end_value = DatumGetInt32(value);
-	exec_eval_cleanup(estate);
-
-	/*
-	 * Get the step value
-	 */
-	if (stmt->step)
+	foreach(lc, stmt->inlist)
 	{
-		value = exec_eval_expr(estate, stmt->step,
-							   &isnull, &valtype, &valtypmod);
+		PLpgSQL_fori_in_item *in_item = (PLpgSQL_fori_in_item *) lfirst(lc);
+		/*
+		* Get the value of the lower bound
+		*/
+		value = exec_eval_expr(estate, in_item->lower,
+							&isnull, &valtype, &valtypmod);
 		value = exec_cast_value(estate, value, &isnull,
 								valtype, valtypmod,
 								var->datatype->typoid,
@@ -2724,75 +2695,110 @@ exec_stmt_fori(PLpgSQL_execstate *estate, PLpgSQL_stmt_fori *stmt)
 		if (isnull)
 			ereport(ERROR,
 					(errcode(ERRCODE_NULL_VALUE_NOT_ALLOWED),
-					 errmsg("BY value of FOR loop cannot be null")));
-		step_value = DatumGetInt32(value);
+					errmsg("lower bound of FOR loop cannot be null")));
+		loop_value = DatumGetInt32(value);
 		exec_eval_cleanup(estate);
-		if (step_value <= 0)
+
+		/*
+		* Get the value of the upper bound
+		*/
+		value = exec_eval_expr(estate, in_item->upper,
+							&isnull, &valtype, &valtypmod);
+		value = exec_cast_value(estate, value, &isnull,
+								valtype, valtypmod,
+								var->datatype->typoid,
+								var->datatype->atttypmod);
+		if (isnull)
 			ereport(ERROR,
-					(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
-					 errmsg("BY value of FOR loop must be greater than zero")));
-	}
-	else
-		step_value = 1;
+					(errcode(ERRCODE_NULL_VALUE_NOT_ALLOWED),
+					errmsg("upper bound of FOR loop cannot be null")));
+		end_value = DatumGetInt32(value);
+		exec_eval_cleanup(estate);
 
-	/*
-	 * Now do the loop
-	 */
-	for (;;)
-	{
 		/*
-		 * Check against upper bound
-		 */
-		if (stmt->reverse)
+		* Get the step value
+		*/
+		if (in_item->step)
 		{
-			if (loop_value < end_value)
-				break;
+			value = exec_eval_expr(estate, in_item->step,
+								&isnull, &valtype, &valtypmod);
+			value = exec_cast_value(estate, value, &isnull,
+									valtype, valtypmod,
+									var->datatype->typoid,
+									var->datatype->atttypmod);
+			if (isnull)
+				ereport(ERROR,
+						(errcode(ERRCODE_NULL_VALUE_NOT_ALLOWED),
+						errmsg("BY value of FOR loop cannot be null")));
+			step_value = DatumGetInt32(value);
+			exec_eval_cleanup(estate);
+			if (step_value <= 0)
+				ereport(ERROR,
+						(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+						errmsg("BY value of FOR loop must be greater than zero")));
 		}
 		else
+			step_value = 1;
+
+		/*
+		* Now do the loop
+		*/
+		for (;;)
 		{
-			if (loop_value > end_value)
-				break;
-		}
+			/*
+			* Check against upper bound
+			*/
+			if (in_item->reverse)
+			{
+				if (loop_value < end_value)
+					break;
+			}
+			else
+			{
+				if (loop_value > end_value)
+					break;
+			}
 
-		found = true;			/* looped at least once */
+			found = true;			/* looped at least once */
 
-		/*
-		 * Assign current value to loop var
-		 */
-		assign_simple_var(estate, var, Int32GetDatum(loop_value), false, false);
+			/*
+			* Assign current value to loop var
+			*/
+			assign_simple_var(estate, var, Int32GetDatum(loop_value), false, false);
 
-		/*
-		 * Execute the statements
-		 */
-		rc = exec_stmts(estate, stmt->body);
+			/*
+			* Execute the statements
+			*/
+			rc = exec_stmts(estate, stmt->body);
 
-		LOOP_RC_PROCESSING(stmt->label, break);
+			LOOP_RC_PROCESSING(stmt->label, break);
 
-		/*
-		 * Increase/decrease loop value, unless it would overflow, in which
-		 * case exit the loop.
-		 */
-		if (stmt->reverse)
-		{
-			if (loop_value < (PG_INT32_MIN + step_value))
-				break;
-			loop_value -= step_value;
-		}
-		else
-		{
-			if (loop_value > (PG_INT32_MAX - step_value))
-				break;
-			loop_value += step_value;
+			/*
+			* Increase/decrease loop value, unless it would overflow, in which
+			* case exit the loop.
+			*/
+			if (in_item->reverse)
+			{
+				if (loop_value < (PG_INT32_MIN + step_value))
+					break;
+				loop_value -= step_value;
+			}
+			else
+			{
+				if (loop_value > (PG_INT32_MAX - step_value))
+					break;
+				loop_value += step_value;
+			}
 		}
-	}
 
-	/*
-	 * Set the FOUND variable to indicate the result of executing the loop
-	 * (namely, whether we looped one or more times). This must be set here so
-	 * that it does not interfere with the value of the FOUND variable inside
-	 * the loop processing itself.
-	 */
-	exec_set_found(estate, found);
+		/*
+		* Set the FOUND variable to indicate the result of executing the loop
+		* (namely, whether we looped one or more times). This must be set here so
+		* that it does not interfere with the value of the FOUND variable inside
+		* the loop processing itself.
+		*/
+		exec_set_found(estate, found);
+	}
 
 	return rc;
 }
diff --git a/src/pl/plpgsql/src/pl_funcs.c b/src/pl/plpgsql/src/pl_funcs.c
index 5a6eadccd5..7f2c219993 100644
--- a/src/pl/plpgsql/src/pl_funcs.c
+++ b/src/pl/plpgsql/src/pl_funcs.c
@@ -546,9 +546,6 @@ free_while(PLpgSQL_stmt_while *stmt)
 static void
 free_fori(PLpgSQL_stmt_fori *stmt)
 {
-	free_expr(stmt->lower);
-	free_expr(stmt->upper);
-	free_expr(stmt->step);
 	free_stmts(stmt->body);
 }
 
@@ -1076,26 +1073,33 @@ dump_while(PLpgSQL_stmt_while *stmt)
 static void
 dump_fori(PLpgSQL_stmt_fori *stmt)
 {
-	dump_ind();
-	printf("FORI %s %s\n", stmt->var->refname, (stmt->reverse) ? "REVERSE" : "NORMAL");
+	ListCell   *lc;
 
-	dump_indent += 2;
 	dump_ind();
-	printf("    lower = ");
-	dump_expr(stmt->lower);
-	printf("\n");
-	dump_ind();
-	printf("    upper = ");
-	dump_expr(stmt->upper);
-	printf("\n");
-	if (stmt->step)
+	printf("FORI %s\n", stmt->var->refname);
+	foreach(lc, stmt->inlist)
 	{
+		PLpgSQL_fori_in_item *in_item = (PLpgSQL_fori_in_item *) lfirst(lc);
+
+		dump_indent += 2;
 		dump_ind();
-		printf("    step = ");
-		dump_expr(stmt->step);
+		printf("    %s", (in_item->reverse) ? "REVERSE" : "NORMAL");
+
+		printf(" lower = ");
+		dump_expr(in_item->lower);
+		printf(" ,");
+		printf(" upper = ");
+		dump_expr(in_item->upper);
+
+		if (in_item->step)
+		{
+			printf(" , ");
+			printf("step = ");
+			dump_expr(in_item->step);
+		}
 		printf("\n");
+		dump_indent -= 2;
 	}
-	dump_indent -= 2;
 
 	dump_stmts(stmt->body);
 
diff --git a/src/pl/plpgsql/src/pl_gram.y b/src/pl/plpgsql/src/pl_gram.y
index edeb72c380..8b34f48b88 100644
--- a/src/pl/plpgsql/src/pl_gram.y
+++ b/src/pl/plpgsql/src/pl_gram.y
@@ -74,6 +74,9 @@ static	PLpgSQL_expr	*read_sql_expression(int until,
 static	PLpgSQL_expr	*read_sql_expression2(int until, int until2,
 											  const char *expected,
 											  int *endtoken);
+static	PLpgSQL_expr	*read_sql_expression3(int until, int until2, int until3,
+											  const char *expected,
+											  int *endtoken);
 static	PLpgSQL_expr	*read_sql_stmt(void);
 static	PLpgSQL_type	*read_datatype(int tok);
 static	PLpgSQL_stmt	*make_execsql_stmt(int firsttoken, int location);
@@ -1434,6 +1437,7 @@ for_control		: for_variable K_IN
 							PLpgSQL_expr *expr1;
 							int			expr1loc;
 							bool		reverse = false;
+							bool		firstflag = true;
 
 							/*
 							 * We have to distinguish between two
@@ -1473,32 +1477,92 @@ for_control		: for_variable K_IN
 
 							if (tok == DOT_DOT)
 							{
-								/* Saw "..", so it must be an integer loop */
-								PLpgSQL_expr *expr2;
-								PLpgSQL_expr *expr_by;
 								PLpgSQL_var	*fvar;
 								PLpgSQL_stmt_fori *new;
 
-								/*
-								 * Relabel first expression as an expression;
-								 * then we can check its syntax.
-								 */
-								expr1->parseMode = RAW_PARSE_PLPGSQL_EXPR;
-								check_sql_expr(expr1->query, expr1->parseMode,
-											   expr1loc);
-
-								/* Read and check the second one */
-								expr2 = read_sql_expression2(K_LOOP, K_BY,
-															 "LOOP",
-															 &tok);
+								new = palloc0(sizeof(PLpgSQL_stmt_fori));
+								new->cmd_type = PLPGSQL_STMT_FORI;
 
-								/* Get the BY clause if any */
-								if (tok == K_BY)
-									expr_by = read_sql_expression(K_LOOP,
-																  "LOOP");
-								else
-									expr_by = NULL;
+								for (;;)
+								{
+									bool		reverseflag = false;
 
+									if(!firstflag)
+									{
+										if (tok_is_keyword(tok, &yylval,
+														K_REVERSE, "reverse"))
+											reverseflag = true;
+										else
+											plpgsql_push_back_token(tok);
+
+										/*
+										* We read the token again until we see ".." or LOOP,
+										* and likewise tell it not to check syntax.
+										*/
+										expr1 = read_sql_construct(DOT_DOT,
+																',',
+																K_LOOP,
+																", or loop",
+																RAW_PARSE_DEFAULT,
+																true,
+																false,
+																true,
+																&expr1loc,
+																&tok);
+									}
+
+									if (tok == DOT_DOT)
+									{
+										/* Saw "..", so it must be an integer loop */
+										PLpgSQL_expr *expr2;
+										PLpgSQL_expr *expr_by;
+										PLpgSQL_fori_in_item *in_item;
+
+										/*
+										* Relabel first expression as an expression;
+										* then we can check its syntax.
+										*/
+										expr1->parseMode = RAW_PARSE_PLPGSQL_EXPR;
+										check_sql_expr(expr1->query, expr1->parseMode,
+													expr1loc);
+
+										/* Read and check the second one */
+										expr2 = read_sql_expression3(K_BY, ',', K_LOOP,
+																	"by , or loop",
+																	&tok);
+
+										/* Get the BY clause if any */
+										if (tok == K_BY)
+											expr_by = read_sql_expression2(',', K_LOOP,
+																		", or K_LOOP", &tok);
+										else
+											expr_by = NULL;
+
+										in_item = palloc0(sizeof(PLpgSQL_fori_in_item));
+										in_item->reverse = firstflag ? reverse : reverseflag;
+										in_item->lower = expr1;
+										in_item->upper = expr2;
+										in_item->step = expr_by;
+										firstflag = false;
+
+										new->inlist = lappend(new->inlist, in_item);
+
+										/* check for in condition list */
+										if (tok == ',')
+										{
+											tok = yylex();
+											continue;
+										}
+										else if(tok == K_LOOP)
+										{
+											break;
+										}
+										else
+										{
+											yyerror("syntax error");
+										}
+									}
+								}
 								/* Should have had a single variable name */
 								if ($1.scalar && $1.row)
 									ereport(ERROR,
@@ -1515,15 +1579,8 @@ for_control		: for_variable K_IN
 																				  InvalidOid,
 																				  NULL),
 														   true);
-
-								new = palloc0(sizeof(PLpgSQL_stmt_fori));
-								new->cmd_type = PLPGSQL_STMT_FORI;
 								new->stmtid	= ++plpgsql_curr_compile->nstatements;
 								new->var = fvar;
-								new->reverse = reverse;
-								new->lower = expr1;
-								new->upper = expr2;
-								new->step = expr_by;
 
 								$$ = (PLpgSQL_stmt *) new;
 							}
@@ -2645,6 +2702,16 @@ read_sql_expression2(int until, int until2, const char *expected,
 							  true, true, true, NULL, endtoken);
 }
 
+/* Convenience routine to read an expression with three possible terminators */
+static PLpgSQL_expr *
+read_sql_expression3(int until, int until2, int until3, const char *expected,
+					 int *endtoken)
+{
+	return read_sql_construct(until, until2, until3, expected,
+							  RAW_PARSE_PLPGSQL_EXPR,
+							  true, true, true, NULL, endtoken);
+}
+
 /* Convenience routine to read a SQL statement that must end with ';' */
 static PLpgSQL_expr *
 read_sql_stmt(void)
diff --git a/src/pl/plpgsql/src/plpgsql.h b/src/pl/plpgsql/src/plpgsql.h
index 355c9f678d..ab35e8fd72 100644
--- a/src/pl/plpgsql/src/plpgsql.h
+++ b/src/pl/plpgsql/src/plpgsql.h
@@ -660,6 +660,14 @@ typedef struct PLpgSQL_stmt_while
 	List	   *body;			/* List of statements */
 } PLpgSQL_stmt_while;
 
+typedef struct PLpgSQL_fori_in_item
+{
+	PLpgSQL_expr *lower;
+	PLpgSQL_expr *upper;
+	PLpgSQL_expr *step;			/* NULL means default (ie, BY 1) */
+	int			reverse;
+}PLpgSQL_fori_in_item;
+
 /*
  * FOR statement with integer loopvar
  */
@@ -670,10 +678,7 @@ typedef struct PLpgSQL_stmt_fori
 	unsigned int stmtid;
 	char	   *label;
 	PLpgSQL_var *var;
-	PLpgSQL_expr *lower;
-	PLpgSQL_expr *upper;
-	PLpgSQL_expr *step;			/* NULL means default (ie, BY 1) */
-	int			reverse;
+	List	   *inlist;			/* List of in conditions */
 	List	   *body;			/* List of statements */
 } PLpgSQL_stmt_fori;
 
diff --git a/src/pl/plpgsql/src/sql/plpgsql_control.sql b/src/pl/plpgsql/src/sql/plpgsql_control.sql
index ed7231134f..b60097b6e3 100644
--- a/src/pl/plpgsql/src/sql/plpgsql_control.sql
+++ b/src/pl/plpgsql/src/sql/plpgsql_control.sql
@@ -58,6 +58,63 @@ begin
   end loop;
 end$$;
 
+-- Test in condition list
+
+do $$
+declare
+	i int;
+begin
+	for i in 1..3 , 51..55 loop
+		raise notice '%', i;
+	end loop;
+
+  for i in 1..3 , reverse 55..51 loop
+		raise info '%', i;
+	end loop;
+	
+	for i in reverse 1..3 loop
+		raise notice '%', i;
+	end loop;
+	
+	for i in 1..3 loop
+		raise notice '%', i;
+	end loop;
+	
+	for i in reverse 3..1 loop
+		raise notice '%', i;
+	end loop;
+	
+	for i in 1..10 by 3 loop
+		raise notice '1..10 by 3: i = %', i;
+	end loop;
+end$$;
+
+do $$
+declare
+   i int := 10;
+begin
+   for i in reverse i+10..i+1 loop
+      raise info '%', i;
+   end loop;
+end $$;
+
+do $$
+declare
+   j int := 10;
+begin
+   for i in 1..3, reverse j+10..j+1 loop
+      raise info '%', i;
+   end loop;
+end $$;
+
+do $$
+declare
+   j int := 10;
+begin
+   for i in reverse j+10..j+1 loop
+      raise info '%', i;
+   end loop;
+end $$;
 
 -- CONTINUE statement
 
-- 
2.36.1.windows.1

Pavel Stehule

pavel.stehule@gmail.com

almost 3 years ago

In reply to: 2903807914@qq.com (#1)

Re: Support plpgsql multi-range in conditional control

č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

Show quoted text

------------------------------
2903807914@qq.com

2903807914@qq.com

almost 3 years ago

In reply to: 2903807914@qq.com (#1)

Re: Re: Support plpgsql multi-range in conditional control

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:

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

Tom Lane

tgl@sss.pgh.pa.us

almost 3 years ago

In reply to: Pavel Stehule (#2)

Re: Support plpgsql multi-range in conditional control

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

Pavel Stehule

pavel.stehule@gmail.com

almost 3 years ago

In reply to: 2903807914@qq.com (#3)

Re: Re: Support plpgsql multi-range in conditional control

č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

Show quoted text

Thanks again!
------------------------------
songjinzhou (2903807914@qq.com)

*From:* Pavel Stehule <pavel.stehule@gmail.com>
*Date:* 2023-01-19 21:04
*To:* 2903807914@qq.com
*CC:* pgsql-hackers <pgsql-hackers@lists.postgresql.org>; 1276576182
<1276576182@qq.com>
*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

almost 3 years ago

In reply to: Pavel Stehule (#5)

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

songjinzhou

2903807914@qq.com

almost 3 years ago

In reply to: 2903807914@qq.com (#1)

5 attachment(s)

Re: Re: Support plpgsql multi-range in conditional control

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.

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);

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

Attachments:

Catch.jpgimage/jpeg; name=Catch.jpgDownload

Catch241A.jpgimage/jpeg; name=Catch241A.jpgDownload

CatchA3E5.jpgimage/jpeg; name=CatchA3E5.jpgDownload

CatchF31A.jpgimage/jpeg; name=CatchF31A.jpgDownload

v2-0001-Support-plpgsql-multi-range-in-conditional-control.patchapplication/octet-stream; name=v2-0001-Support-plpgsql-multi-range-in-conditional-control.patchDownload

From afbd699904a7cd44560fca2da38894c3cf2366de Mon Sep 17 00:00:00 2001
From: TsinghuaLucky912 <2903807914@qq.com>
Date: Fri, 20 Jan 2023 11:11:07 +0800
Subject: [PATCH] Support plpgsql multi-range in conditional control

---
 doc/src/sgml/plpgsql.sgml                     |  15 +-
 .../plpgsql/src/expected/plpgsql_control.out  | 182 +++++++++++++++++
 src/pl/plpgsql/src/pl_exec.c                  | 189 +++++++++---------
 src/pl/plpgsql/src/pl_funcs.c                 |  38 ++--
 src/pl/plpgsql/src/pl_gram.y                  | 130 +++++++++---
 src/pl/plpgsql/src/plpgsql.h                  |  13 +-
 src/pl/plpgsql/src/sql/plpgsql_control.sql    |  86 ++++++++
 7 files changed, 511 insertions(+), 142 deletions(-)

diff --git a/doc/src/sgml/plpgsql.sgml b/doc/src/sgml/plpgsql.sgml
index 8897a5450a..edf523593a 100644
--- a/doc/src/sgml/plpgsql.sgml
+++ b/doc/src/sgml/plpgsql.sgml
@@ -2518,7 +2518,7 @@ END LOOP;
 
 <synopsis>
 <optional> &lt;&lt;<replaceable>label</replaceable>&gt;&gt; </optional>
-FOR <replaceable>name</replaceable> IN <optional> REVERSE </optional> <replaceable>expression</replaceable> .. <replaceable>expression</replaceable> <optional> BY <replaceable>expression</replaceable> </optional> LOOP
+FOR <replaceable>name</replaceable> IN <replaceable>condition_iterator</replaceable> <optional>, <replaceable>condition_iterator</replaceable> <optional> ... </optional></optional> LOOP
     <replaceable>statements</replaceable>
 END LOOP <optional> <replaceable>label</replaceable> </optional>;
 </synopsis>
@@ -2529,6 +2529,15 @@ END LOOP <optional> <replaceable>label</replaceable> </optional>;
         <replaceable>name</replaceable> is automatically defined as type
         <type>integer</type> and exists only inside the loop (any existing
         definition of the variable name is ignored within the loop).
+        </para>
+
+<synopsis>
+condition_iterator:
+<optional> REVERSE </optional> <replaceable>expression</replaceable> .. <replaceable>expression</replaceable> <optional> BY <replaceable>expression</replaceable> </optional>
+</synopsis>
+
+       <para>
+        Multiple iteration controls may be chained together by separating them with commas. The composition of each condition_iterator is as follows:
         The two expressions giving
         the lower and upper bound of the range are evaluated once when entering
         the loop. If the <literal>BY</literal> clause isn't specified the iteration
@@ -2552,6 +2561,10 @@ END LOOP;
 FOR i IN REVERSE 10..1 BY 2 LOOP
     -- i will take on the values 10,8,6,4,2 within the loop
 END LOOP;
+
+FOR I IN 1..10 BY 3, REVERSE I+10..I+1 BY 3 LOOP
+    -- i will take on the values 1,4,7,10,20,17,14,11 within the loop
+END LOOP;
 </programlisting>
        </para>
 
diff --git a/src/pl/plpgsql/src/expected/plpgsql_control.out b/src/pl/plpgsql/src/expected/plpgsql_control.out
index 328bd48586..91008c8542 100644
--- a/src/pl/plpgsql/src/expected/plpgsql_control.out
+++ b/src/pl/plpgsql/src/expected/plpgsql_control.out
@@ -79,6 +79,188 @@ begin
 end$$;
 ERROR:  BY value of FOR loop must be greater than zero
 CONTEXT:  PL/pgSQL function inline_code_block line 3 at FOR with integer loop variable
+-- Test in condition list
+do $$
+declare
+	i int;
+begin
+	for i in 1..3 , 51..55 loop
+		raise notice '%', i;
+	end loop;
+
+  for i in 1..3 , reverse 55..51 loop
+		raise info '%', i;
+	end loop;
+
+	for i in reverse 1..3 loop
+		raise notice '%', i;
+	end loop;
+
+	for i in 1..3 loop
+		raise notice '%', i;
+	end loop;
+
+	for i in reverse 3..1 loop
+		raise notice '%', i;
+	end loop;
+
+	for i in 1..10 by 3 loop
+		raise notice '1..10 by 3: i = %', i;
+	end loop;
+end$$;
+NOTICE:  1
+NOTICE:  2
+NOTICE:  3
+NOTICE:  51
+NOTICE:  52
+NOTICE:  53
+NOTICE:  54
+NOTICE:  55
+INFO:  1
+INFO:  2
+INFO:  3
+INFO:  55
+INFO:  54
+INFO:  53
+INFO:  52
+INFO:  51
+NOTICE:  1
+NOTICE:  2
+NOTICE:  3
+NOTICE:  3
+NOTICE:  2
+NOTICE:  1
+NOTICE:  1..10 by 3: i = 1
+NOTICE:  1..10 by 3: i = 4
+NOTICE:  1..10 by 3: i = 7
+NOTICE:  1..10 by 3: i = 10
+do $$
+declare
+   i int := 10;
+begin
+   for i in reverse i+10..i+1 loop
+      raise info '%', i;
+   end loop;
+end $$;
+INFO:  20
+INFO:  19
+INFO:  18
+INFO:  17
+INFO:  16
+INFO:  15
+INFO:  14
+INFO:  13
+INFO:  12
+INFO:  11
+do $$
+declare
+   j int := 10;
+begin
+   for i in 1..3, reverse j+10..j+1 loop
+      raise info '%', i;
+   end loop;
+end $$;
+INFO:  1
+INFO:  2
+INFO:  3
+INFO:  20
+INFO:  19
+INFO:  18
+INFO:  17
+INFO:  16
+INFO:  15
+INFO:  14
+INFO:  13
+INFO:  12
+INFO:  11
+do $$
+declare
+   j int := 10;
+begin
+   for i in reverse j+10..j+1 loop
+      raise info '%', i;
+   end loop;
+end $$;
+INFO:  20
+INFO:  19
+INFO:  18
+INFO:  17
+INFO:  16
+INFO:  15
+INFO:  14
+INFO:  13
+INFO:  12
+INFO:  11
+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 $$;
+NOTICE:  10
+NOTICE:  11
+NOTICE:  12
+NOTICE:  13
+NOTICE:  14
+NOTICE:  15
+NOTICE:  16
+NOTICE:  17
+NOTICE:  18
+NOTICE:  19
+NOTICE:  20
+NOTICE:  21
+NOTICE:  100
+NOTICE:  110
+NOTICE:  120
+NOTICE:  130
+NOTICE:  140
+NOTICE:  150
+NOTICE:  160
+NOTICE:  170
+NOTICE:  180
+NOTICE:  190
+NOTICE:  200
+drop type range_expr;
+do $$
+begin
+  for i in 10..20
+  loop
+    raise notice 'Scenario 1: %', i ; -- Scenario 1
+  end loop;
+
+  for i in 100 .. 200 by 10
+  loop
+    raise notice 'Scenario 2: %', i; -- Scenario 2
+  end loop;
+end $$;
+NOTICE:  Scenario 1: 10
+NOTICE:  Scenario 1: 11
+NOTICE:  Scenario 1: 12
+NOTICE:  Scenario 1: 13
+NOTICE:  Scenario 1: 14
+NOTICE:  Scenario 1: 15
+NOTICE:  Scenario 1: 16
+NOTICE:  Scenario 1: 17
+NOTICE:  Scenario 1: 18
+NOTICE:  Scenario 1: 19
+NOTICE:  Scenario 1: 20
+NOTICE:  Scenario 2: 100
+NOTICE:  Scenario 2: 110
+NOTICE:  Scenario 2: 120
+NOTICE:  Scenario 2: 130
+NOTICE:  Scenario 2: 140
+NOTICE:  Scenario 2: 150
+NOTICE:  Scenario 2: 160
+NOTICE:  Scenario 2: 170
+NOTICE:  Scenario 2: 180
+NOTICE:  Scenario 2: 190
+NOTICE:  Scenario 2: 200
 -- CONTINUE statement
 create table conttesttbl(idx serial, v integer);
 insert into conttesttbl(v) values(10);
diff --git a/src/pl/plpgsql/src/pl_exec.c b/src/pl/plpgsql/src/pl_exec.c
index 37da624388..b38c393145 100644
--- a/src/pl/plpgsql/src/pl_exec.c
+++ b/src/pl/plpgsql/src/pl_exec.c
@@ -2675,48 +2675,18 @@ exec_stmt_fori(PLpgSQL_execstate *estate, PLpgSQL_stmt_fori *stmt)
 	int32		step_value;
 	bool		found = false;
 	int			rc = PLPGSQL_RC_OK;
+	ListCell	*lc;
 
 	var = (PLpgSQL_var *) (estate->datums[stmt->var->dno]);
 
-	/*
-	 * Get the value of the lower bound
-	 */
-	value = exec_eval_expr(estate, stmt->lower,
-						   &isnull, &valtype, &valtypmod);
-	value = exec_cast_value(estate, value, &isnull,
-							valtype, valtypmod,
-							var->datatype->typoid,
-							var->datatype->atttypmod);
-	if (isnull)
-		ereport(ERROR,
-				(errcode(ERRCODE_NULL_VALUE_NOT_ALLOWED),
-				 errmsg("lower bound of FOR loop cannot be null")));
-	loop_value = DatumGetInt32(value);
-	exec_eval_cleanup(estate);
-
-	/*
-	 * Get the value of the upper bound
-	 */
-	value = exec_eval_expr(estate, stmt->upper,
-						   &isnull, &valtype, &valtypmod);
-	value = exec_cast_value(estate, value, &isnull,
-							valtype, valtypmod,
-							var->datatype->typoid,
-							var->datatype->atttypmod);
-	if (isnull)
-		ereport(ERROR,
-				(errcode(ERRCODE_NULL_VALUE_NOT_ALLOWED),
-				 errmsg("upper bound of FOR loop cannot be null")));
-	end_value = DatumGetInt32(value);
-	exec_eval_cleanup(estate);
-
-	/*
-	 * Get the step value
-	 */
-	if (stmt->step)
+	foreach(lc, stmt->inlist)
 	{
-		value = exec_eval_expr(estate, stmt->step,
-							   &isnull, &valtype, &valtypmod);
+		PLpgSQL_fori_in_item *in_item = (PLpgSQL_fori_in_item *) lfirst(lc);
+		/*
+		* Get the value of the lower bound
+		*/
+		value = exec_eval_expr(estate, in_item->lower,
+							&isnull, &valtype, &valtypmod);
 		value = exec_cast_value(estate, value, &isnull,
 								valtype, valtypmod,
 								var->datatype->typoid,
@@ -2724,75 +2694,110 @@ exec_stmt_fori(PLpgSQL_execstate *estate, PLpgSQL_stmt_fori *stmt)
 		if (isnull)
 			ereport(ERROR,
 					(errcode(ERRCODE_NULL_VALUE_NOT_ALLOWED),
-					 errmsg("BY value of FOR loop cannot be null")));
-		step_value = DatumGetInt32(value);
+					errmsg("lower bound of FOR loop cannot be null")));
+		loop_value = DatumGetInt32(value);
 		exec_eval_cleanup(estate);
-		if (step_value <= 0)
+
+		/*
+		* Get the value of the upper bound
+		*/
+		value = exec_eval_expr(estate, in_item->upper,
+							&isnull, &valtype, &valtypmod);
+		value = exec_cast_value(estate, value, &isnull,
+								valtype, valtypmod,
+								var->datatype->typoid,
+								var->datatype->atttypmod);
+		if (isnull)
 			ereport(ERROR,
-					(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
-					 errmsg("BY value of FOR loop must be greater than zero")));
-	}
-	else
-		step_value = 1;
+					(errcode(ERRCODE_NULL_VALUE_NOT_ALLOWED),
+					errmsg("upper bound of FOR loop cannot be null")));
+		end_value = DatumGetInt32(value);
+		exec_eval_cleanup(estate);
 
-	/*
-	 * Now do the loop
-	 */
-	for (;;)
-	{
 		/*
-		 * Check against upper bound
-		 */
-		if (stmt->reverse)
+		* Get the step value
+		*/
+		if (in_item->step)
 		{
-			if (loop_value < end_value)
-				break;
+			value = exec_eval_expr(estate, in_item->step,
+								&isnull, &valtype, &valtypmod);
+			value = exec_cast_value(estate, value, &isnull,
+									valtype, valtypmod,
+									var->datatype->typoid,
+									var->datatype->atttypmod);
+			if (isnull)
+				ereport(ERROR,
+						(errcode(ERRCODE_NULL_VALUE_NOT_ALLOWED),
+						errmsg("BY value of FOR loop cannot be null")));
+			step_value = DatumGetInt32(value);
+			exec_eval_cleanup(estate);
+			if (step_value <= 0)
+				ereport(ERROR,
+						(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+						errmsg("BY value of FOR loop must be greater than zero")));
 		}
 		else
+			step_value = 1;
+
+		/*
+		* Now do the loop
+		*/
+		for (;;)
 		{
-			if (loop_value > end_value)
-				break;
-		}
+			/*
+			* Check against upper bound
+			*/
+			if (in_item->reverse)
+			{
+				if (loop_value < end_value)
+					break;
+			}
+			else
+			{
+				if (loop_value > end_value)
+					break;
+			}
 
-		found = true;			/* looped at least once */
+			found = true;			/* looped at least once */
 
-		/*
-		 * Assign current value to loop var
-		 */
-		assign_simple_var(estate, var, Int32GetDatum(loop_value), false, false);
+			/*
+			* Assign current value to loop var
+			*/
+			assign_simple_var(estate, var, Int32GetDatum(loop_value), false, false);
 
-		/*
-		 * Execute the statements
-		 */
-		rc = exec_stmts(estate, stmt->body);
+			/*
+			* Execute the statements
+			*/
+			rc = exec_stmts(estate, stmt->body);
 
-		LOOP_RC_PROCESSING(stmt->label, break);
+			LOOP_RC_PROCESSING(stmt->label, break);
 
-		/*
-		 * Increase/decrease loop value, unless it would overflow, in which
-		 * case exit the loop.
-		 */
-		if (stmt->reverse)
-		{
-			if (loop_value < (PG_INT32_MIN + step_value))
-				break;
-			loop_value -= step_value;
-		}
-		else
-		{
-			if (loop_value > (PG_INT32_MAX - step_value))
-				break;
-			loop_value += step_value;
+			/*
+			* Increase/decrease loop value, unless it would overflow, in which
+			* case exit the loop.
+			*/
+			if (in_item->reverse)
+			{
+				if (loop_value < (PG_INT32_MIN + step_value))
+					break;
+				loop_value -= step_value;
+			}
+			else
+			{
+				if (loop_value > (PG_INT32_MAX - step_value))
+					break;
+				loop_value += step_value;
+			}
 		}
-	}
 
-	/*
-	 * Set the FOUND variable to indicate the result of executing the loop
-	 * (namely, whether we looped one or more times). This must be set here so
-	 * that it does not interfere with the value of the FOUND variable inside
-	 * the loop processing itself.
-	 */
-	exec_set_found(estate, found);
+		/*
+		* Set the FOUND variable to indicate the result of executing the loop
+		* (namely, whether we looped one or more times). This must be set here so
+		* that it does not interfere with the value of the FOUND variable inside
+		* the loop processing itself.
+		*/
+		exec_set_found(estate, found);
+	}
 
 	return rc;
 }
diff --git a/src/pl/plpgsql/src/pl_funcs.c b/src/pl/plpgsql/src/pl_funcs.c
index 5a6eadccd5..7f2c219993 100644
--- a/src/pl/plpgsql/src/pl_funcs.c
+++ b/src/pl/plpgsql/src/pl_funcs.c
@@ -546,9 +546,6 @@ free_while(PLpgSQL_stmt_while *stmt)
 static void
 free_fori(PLpgSQL_stmt_fori *stmt)
 {
-	free_expr(stmt->lower);
-	free_expr(stmt->upper);
-	free_expr(stmt->step);
 	free_stmts(stmt->body);
 }
 
@@ -1076,26 +1073,33 @@ dump_while(PLpgSQL_stmt_while *stmt)
 static void
 dump_fori(PLpgSQL_stmt_fori *stmt)
 {
-	dump_ind();
-	printf("FORI %s %s\n", stmt->var->refname, (stmt->reverse) ? "REVERSE" : "NORMAL");
+	ListCell   *lc;
 
-	dump_indent += 2;
 	dump_ind();
-	printf("    lower = ");
-	dump_expr(stmt->lower);
-	printf("\n");
-	dump_ind();
-	printf("    upper = ");
-	dump_expr(stmt->upper);
-	printf("\n");
-	if (stmt->step)
+	printf("FORI %s\n", stmt->var->refname);
+	foreach(lc, stmt->inlist)
 	{
+		PLpgSQL_fori_in_item *in_item = (PLpgSQL_fori_in_item *) lfirst(lc);
+
+		dump_indent += 2;
 		dump_ind();
-		printf("    step = ");
-		dump_expr(stmt->step);
+		printf("    %s", (in_item->reverse) ? "REVERSE" : "NORMAL");
+
+		printf(" lower = ");
+		dump_expr(in_item->lower);
+		printf(" ,");
+		printf(" upper = ");
+		dump_expr(in_item->upper);
+
+		if (in_item->step)
+		{
+			printf(" , ");
+			printf("step = ");
+			dump_expr(in_item->step);
+		}
 		printf("\n");
+		dump_indent -= 2;
 	}
-	dump_indent -= 2;
 
 	dump_stmts(stmt->body);
 
diff --git a/src/pl/plpgsql/src/pl_gram.y b/src/pl/plpgsql/src/pl_gram.y
index edeb72c380..d52e094bda 100644
--- a/src/pl/plpgsql/src/pl_gram.y
+++ b/src/pl/plpgsql/src/pl_gram.y
@@ -74,6 +74,9 @@ static	PLpgSQL_expr	*read_sql_expression(int until,
 static	PLpgSQL_expr	*read_sql_expression2(int until, int until2,
 											  const char *expected,
 											  int *endtoken);
+static	PLpgSQL_expr	*read_sql_expression3(int until, int until2, int until3,
+											  const char *expected,
+											  int *endtoken);
 static	PLpgSQL_expr	*read_sql_stmt(void);
 static	PLpgSQL_type	*read_datatype(int tok);
 static	PLpgSQL_stmt	*make_execsql_stmt(int firsttoken, int location);
@@ -1434,6 +1437,7 @@ for_control		: for_variable K_IN
 							PLpgSQL_expr *expr1;
 							int			expr1loc;
 							bool		reverse = false;
+							bool		firstflag = true;
 
 							/*
 							 * We have to distinguish between two
@@ -1473,32 +1477,99 @@ for_control		: for_variable K_IN
 
 							if (tok == DOT_DOT)
 							{
-								/* Saw "..", so it must be an integer loop */
-								PLpgSQL_expr *expr2;
-								PLpgSQL_expr *expr_by;
 								PLpgSQL_var	*fvar;
 								PLpgSQL_stmt_fori *new;
 
-								/*
-								 * Relabel first expression as an expression;
-								 * then we can check its syntax.
-								 */
-								expr1->parseMode = RAW_PARSE_PLPGSQL_EXPR;
-								check_sql_expr(expr1->query, expr1->parseMode,
-											   expr1loc);
-
-								/* Read and check the second one */
-								expr2 = read_sql_expression2(K_LOOP, K_BY,
-															 "LOOP",
-															 &tok);
+								new = palloc0(sizeof(PLpgSQL_stmt_fori));
+								new->cmd_type = PLPGSQL_STMT_FORI;
 
-								/* Get the BY clause if any */
-								if (tok == K_BY)
-									expr_by = read_sql_expression(K_LOOP,
-																  "LOOP");
-								else
-									expr_by = NULL;
+								for (;;)
+								{
+									bool		reverseflag = false;
 
+									if(!firstflag)
+									{
+										if (tok_is_keyword(tok, &yylval,
+														K_REVERSE, "reverse"))
+											reverseflag = true;
+										else
+											plpgsql_push_back_token(tok);
+
+										/*
+										* We read the token again until we see ".." or LOOP,
+										* and likewise tell it not to check syntax.
+										*/
+										expr1 = read_sql_construct(DOT_DOT,
+																',',
+																K_LOOP,
+																", or loop",
+																RAW_PARSE_DEFAULT,
+																true,
+																false,
+																true,
+																&expr1loc,
+																&tok);
+									}
+
+									if (tok == DOT_DOT)
+									{
+										/* Saw "..", so it must be an integer loop */
+										PLpgSQL_expr *expr2;
+										PLpgSQL_expr *expr_by;
+										PLpgSQL_fori_in_item *in_item;
+
+										/*
+										* Relabel first expression as an expression;
+										* then we can check its syntax.
+										*/
+										expr1->parseMode = RAW_PARSE_PLPGSQL_EXPR;
+										check_sql_expr(expr1->query, expr1->parseMode,
+													expr1loc);
+
+										/* Read and check the second one */
+										expr2 = read_sql_expression3(K_BY, ',', K_LOOP,
+																	"by , or loop",
+																	&tok);
+
+										/* Get the BY clause if any */
+										if (tok == K_BY)
+											expr_by = read_sql_expression2(',', K_LOOP,
+																		", or K_LOOP", &tok);
+										else
+											expr_by = NULL;
+
+										in_item = palloc0(sizeof(PLpgSQL_fori_in_item));
+										in_item->reverse = firstflag ? reverse : reverseflag;
+										in_item->lower = expr1;
+										in_item->upper = expr2;
+										in_item->step = expr_by;
+										firstflag = false;
+
+										new->inlist = lappend(new->inlist, in_item);
+
+										/* check for in condition list */
+										if (tok == ',')
+										{
+											tok = yylex();
+											continue;
+										}
+										else if(tok == K_LOOP)
+										{
+											break;
+										}
+										else
+										{
+											yyerror("syntax error");
+										}
+									}
+									else
+									{
+										ereport(ERROR,
+											(errcode(ERRCODE_SYNTAX_ERROR),
+											 errmsg("only integer ranges are allowed in condition_iterator"),
+											 parser_errposition(expr1loc)));
+									}
+								}
 								/* Should have had a single variable name */
 								if ($1.scalar && $1.row)
 									ereport(ERROR,
@@ -1515,15 +1586,8 @@ for_control		: for_variable K_IN
 																				  InvalidOid,
 																				  NULL),
 														   true);
-
-								new = palloc0(sizeof(PLpgSQL_stmt_fori));
-								new->cmd_type = PLPGSQL_STMT_FORI;
 								new->stmtid	= ++plpgsql_curr_compile->nstatements;
 								new->var = fvar;
-								new->reverse = reverse;
-								new->lower = expr1;
-								new->upper = expr2;
-								new->step = expr_by;
 
 								$$ = (PLpgSQL_stmt *) new;
 							}
@@ -2645,6 +2709,16 @@ read_sql_expression2(int until, int until2, const char *expected,
 							  true, true, true, NULL, endtoken);
 }
 
+/* Convenience routine to read an expression with three possible terminators */
+static PLpgSQL_expr *
+read_sql_expression3(int until, int until2, int until3, const char *expected,
+					 int *endtoken)
+{
+	return read_sql_construct(until, until2, until3, expected,
+							  RAW_PARSE_PLPGSQL_EXPR,
+							  true, true, true, NULL, endtoken);
+}
+
 /* Convenience routine to read a SQL statement that must end with ';' */
 static PLpgSQL_expr *
 read_sql_stmt(void)
diff --git a/src/pl/plpgsql/src/plpgsql.h b/src/pl/plpgsql/src/plpgsql.h
index 355c9f678d..ab35e8fd72 100644
--- a/src/pl/plpgsql/src/plpgsql.h
+++ b/src/pl/plpgsql/src/plpgsql.h
@@ -660,6 +660,14 @@ typedef struct PLpgSQL_stmt_while
 	List	   *body;			/* List of statements */
 } PLpgSQL_stmt_while;
 
+typedef struct PLpgSQL_fori_in_item
+{
+	PLpgSQL_expr *lower;
+	PLpgSQL_expr *upper;
+	PLpgSQL_expr *step;			/* NULL means default (ie, BY 1) */
+	int			reverse;
+}PLpgSQL_fori_in_item;
+
 /*
  * FOR statement with integer loopvar
  */
@@ -670,10 +678,7 @@ typedef struct PLpgSQL_stmt_fori
 	unsigned int stmtid;
 	char	   *label;
 	PLpgSQL_var *var;
-	PLpgSQL_expr *lower;
-	PLpgSQL_expr *upper;
-	PLpgSQL_expr *step;			/* NULL means default (ie, BY 1) */
-	int			reverse;
+	List	   *inlist;			/* List of in conditions */
 	List	   *body;			/* List of statements */
 } PLpgSQL_stmt_fori;
 
diff --git a/src/pl/plpgsql/src/sql/plpgsql_control.sql b/src/pl/plpgsql/src/sql/plpgsql_control.sql
index ed7231134f..a07002ca14 100644
--- a/src/pl/plpgsql/src/sql/plpgsql_control.sql
+++ b/src/pl/plpgsql/src/sql/plpgsql_control.sql
@@ -58,6 +58,92 @@ begin
   end loop;
 end$$;
 
+-- Test in condition list
+
+do $$
+declare
+	i int;
+begin
+	for i in 1..3 , 51..55 loop
+		raise notice '%', i;
+	end loop;
+
+  for i in 1..3 , reverse 55..51 loop
+		raise info '%', i;
+	end loop;
+
+	for i in reverse 1..3 loop
+		raise notice '%', i;
+	end loop;
+
+	for i in 1..3 loop
+		raise notice '%', i;
+	end loop;
+
+	for i in reverse 3..1 loop
+		raise notice '%', i;
+	end loop;
+
+	for i in 1..10 by 3 loop
+		raise notice '1..10 by 3: i = %', i;
+	end loop;
+end$$;
+
+do $$
+declare
+   i int := 10;
+begin
+   for i in reverse i+10..i+1 loop
+      raise info '%', i;
+   end loop;
+end $$;
+
+do $$
+declare
+   j int := 10;
+begin
+   for i in 1..3, reverse j+10..j+1 loop
+      raise info '%', i;
+   end loop;
+end $$;
+
+do $$
+declare
+   j int := 10;
+begin
+   for i in reverse j+10..j+1 loop
+      raise info '%', i;
+   end loop;
+end $$;
+
+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 $$;
+
+drop type range_expr;
+
+do $$
+begin
+  for i in 10..20
+  loop
+    raise notice 'Scenario 1: %', i ; -- Scenario 1
+  end loop;
+
+  for i in 100 .. 200 by 10
+  loop
+    raise notice 'Scenario 2: %', i; -- Scenario 2
+  end loop;
+end $$;
 
 -- CONTINUE statement
 
-- 
2.36.1.windows.1

Pavel Stehule

pavel.stehule@gmail.com

almost 3 years ago

In reply to: songjinzhou (#7)

4 attachment(s)

Re: Re: Support plpgsql multi-range in conditional control

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

songjinzhou

2903807914@qq.com

almost 3 years ago

In reply to: 2903807914@qq.com (#1)

Re: Re: Support plpgsql multi-range in conditional control

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

#10

Pavel Stehule

pavel.stehule@gmail.com

almost 3 years ago

In reply to: songjinzhou (#9)

Re: Re: Support plpgsql multi-range in conditional control

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

Show quoted text

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

#11

songjinzhou

2903807914@qq.com

almost 3 years ago

In reply to: 2903807914@qq.com (#1)

1 attachment(s)

Re: Re: Support plpgsql multi-range in conditional control

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

#12

Pavel Stehule

pavel.stehule@gmail.com

almost 3 years ago

In reply to: songjinzhou (#11)

1 attachment(s)

Re: Re: Support plpgsql multi-range in conditional control

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

Show quoted text

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

#13

songjinzhou

2903807914@qq.com

almost 3 years ago

In reply to: 2903807914@qq.com (#1)

1 attachment(s)

Re: Re: Support plpgsql multi-range in conditional control

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

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

#14

Pavel Stehule

pavel.stehule@gmail.com

almost 3 years ago

In reply to: songjinzhou (#13)

1 attachment(s)

Re: Re: Support plpgsql multi-range in conditional control

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

Show quoted text

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

*From:* Pavel Stehule <pavel.stehule@gmail.com>
*Date:* 2023-01-25 23:24
*To:* songjinzhou <2903807914@qq.com>
*CC:* pgsql-hackers <pgsql-hackers@lists.postgresql.org>
*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 <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

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

#15

songjinzhou

2903807914@qq.com

almost 3 years ago

In reply to: 2903807914@qq.com (#1)

Re: Re: Support plpgsql multi-range in conditional control

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)

#16

Pavel Stehule

pavel.stehule@gmail.com

almost 3 years ago

In reply to: songjinzhou (#15)

Re: Re: Support plpgsql multi-range in conditional control

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

Show quoted text

---

songjinzhou(2903807914@qq.com)

#17

Isaac Morland

isaac.morland@gmail.com

almost 3 years ago

In reply to: Pavel Stehule (#16)

Re: Re: Support plpgsql multi-range in conditional control

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.