From 6e02755b22ea62775c906d29b87b55b38ab70bd2 Mon Sep 17 00:00:00 2001 From: Tom Lane Date: Wed, 16 Feb 2011 01:52:04 -0500 Subject: [PATCH] Add FOREACH IN ARRAY looping to plpgsql. (I'm not entirely sure that we've finished bikeshedding the syntax details, but the functionality seems OK.) Pavel Stehule, reviewed by Stephen Frost and Tom Lane --- doc/src/sgml/plpgsql.sgml | 106 +++++++++++-- src/backend/utils/adt/arrayfuncs.c | 206 ++++++++++++++++++++++++++ src/include/utils/array.h | 7 + src/pl/plpgsql/src/gram.y | 63 +++++++- src/pl/plpgsql/src/pl_exec.c | 185 +++++++++++++++++++++++ src/pl/plpgsql/src/pl_funcs.c | 23 +++ src/pl/plpgsql/src/pl_scanner.c | 3 + src/pl/plpgsql/src/plpgsql.h | 13 ++ src/test/regress/expected/plpgsql.out | 194 ++++++++++++++++++++++++ src/test/regress/sql/plpgsql.sql | 114 ++++++++++++++ 10 files changed, 899 insertions(+), 15 deletions(-) diff --git a/doc/src/sgml/plpgsql.sgml b/doc/src/sgml/plpgsql.sgml index a2601e6bc8..c342916ff3 100644 --- a/doc/src/sgml/plpgsql.sgml +++ b/doc/src/sgml/plpgsql.sgml @@ -1263,7 +1263,7 @@ EXECUTE 'UPDATE tbl SET ' EXECUTE format('UPDATE tbl SET %I = %L WHERE key = %L', colname, newvalue, keyvalue); - The format function can be used in conjunction with + The format function can be used in conjunction with the USING clause: EXECUTE format('UPDATE tbl SET %I = $1 WHERE key = $2', colname) @@ -1356,19 +1356,15 @@ GET DIAGNOSTICS integer_var = ROW_COUNT; true if it successfully repositions the cursor, false otherwise. - - A FOR statement sets FOUND true - if it iterates one or more times, else false. This applies to - all four variants of the FOR statement (integer - FOR loops, record-set FOR loops, - dynamic record-set FOR loops, and cursor - FOR loops). + A FOR or FOREACH statement sets + FOUND true + if it iterates one or more times, else false. FOUND is set this way when the - FOR loop exits; inside the execution of the loop, + loop exits; inside the execution of the loop, FOUND is not modified by the - FOR statement, although it might be changed by the + loop statement, although it might be changed by the execution of other statements within the loop body. @@ -1910,9 +1906,9 @@ END CASE; With the LOOP, EXIT, - CONTINUE, WHILE, and FOR - statements, you can arrange for your PL/pgSQL - function to repeat a series of commands. + CONTINUE, WHILE, FOR, + and FOREACH statements, you can arrange for your + PL/pgSQL function to repeat a series of commands. @@ -2238,6 +2234,90 @@ END LOOP label ; + + Looping Through Arrays + + + The FOREACH loop is much like a FOR loop, + but instead of iterating through the rows returned by a SQL query, + it iterates through the elements of an array value. + (In general, FOREACH is meant for looping through + components of a composite-valued expression; variants for looping + through composites besides arrays may be added in future.) + The FOREACH statement to loop over an array is: + + + <<label>> +FOREACH target SLICE number IN ARRAY expression LOOP + statements +END LOOP label ; + + + + + Without SLICE, or if SLICE 0 is specified, + the loop iterates through individual elements of the array produced + by evaluating the expression. + The target variable is assigned each + element value in sequence, and the loop body is executed for each element. + Here is an example of looping through the elements of an integer + array: + + +CREATE FUNCTION sum(int[]) RETURNS int8 AS $$ +DECLARE + s int8 := 0; + x int; +BEGIN + FOREACH x IN ARRAY $1 + LOOP + s := s + x; + END LOOP; + RETURN s; +END; +$$ LANGUAGE plpgsql; + + + The elements are visited in storage order, regardless of the number of + array dimensions. Although the target is + usually just a single variable, it can be a list of variables when + looping through an array of composite values (records). In that case, + for each array element, the variables are assigned from successive + columns of the composite value. + + + + With a positive SLICE value, FOREACH + iterates through slices of the array rather than single elements. + The SLICE value must be an integer constant not larger + than the number of dimensions of the array. The + target variable must be an array, + and it receives successive slices of the array value, where each slice + is of the number of dimensions specified by SLICE. + Here is an example of iterating through one-dimensional slices: + + +CREATE FUNCTION scan_rows(int[]) RETURNS void AS $$ +DECLARE + x int[]; +BEGIN + FOREACH x SLICE 1 IN ARRAY $1 + LOOP + RAISE NOTICE 'row = %', x; + END LOOP; +END; +$$ LANGUAGE plpgsql; + +SELECT scan_rows(ARRAY[[1,2,3],[4,5,6],[7,8,9],[10,11,12]]); + +NOTICE: row = {1,2,3} +NOTICE: row = {4,5,6} +NOTICE: row = {7,8,9} +NOTICE: row = {10,11,12} + + + + Trapping Errors diff --git a/src/backend/utils/adt/arrayfuncs.c b/src/backend/utils/adt/arrayfuncs.c index 4ac9830878..e023b2458e 100644 --- a/src/backend/utils/adt/arrayfuncs.c +++ b/src/backend/utils/adt/arrayfuncs.c @@ -50,6 +50,30 @@ typedef enum ARRAY_LEVEL_DELIMITED } ArrayParseState; +/* Working state for array_iterate() */ +typedef struct ArrayIteratorData +{ + /* basic info about the array, set up during array_create_iterator() */ + ArrayType *arr; /* array we're iterating through */ + bits8 *nullbitmap; /* its null bitmap, if any */ + int nitems; /* total number of elements in array */ + int16 typlen; /* element type's length */ + bool typbyval; /* element type's byval property */ + char typalign; /* element type's align property */ + + /* information about the requested slice size */ + int slice_ndim; /* slice dimension, or 0 if not slicing */ + int slice_len; /* number of elements per slice */ + int *slice_dims; /* slice dims array */ + int *slice_lbound; /* slice lbound array */ + Datum *slice_values; /* workspace of length slice_len */ + bool *slice_nulls; /* workspace of length slice_len */ + + /* current position information, updated on each iteration */ + char *data_ptr; /* our current position in the array */ + int current_item; /* the item # we're at in the array */ +} ArrayIteratorData; + static bool array_isspace(char ch); static int ArrayCount(const char *str, int *dim, char typdelim); static void ReadArrayStr(char *arrayStr, const char *origStr, @@ -3833,6 +3857,188 @@ arraycontained(PG_FUNCTION_ARGS) } +/*----------------------------------------------------------------------------- + * Array iteration functions + * These functions are used to iterate efficiently through arrays + *----------------------------------------------------------------------------- + */ + +/* + * array_create_iterator --- set up to iterate through an array + * + * If slice_ndim is zero, we will iterate element-by-element; the returned + * datums are of the array's element type. + * + * If slice_ndim is 1..ARR_NDIM(arr), we will iterate by slices: the + * returned datums are of the same array type as 'arr', but of size + * equal to the rightmost N dimensions of 'arr'. + * + * The passed-in array must remain valid for the lifetime of the iterator. + */ +ArrayIterator +array_create_iterator(ArrayType *arr, int slice_ndim) +{ + ArrayIterator iterator = palloc0(sizeof(ArrayIteratorData)); + + /* + * Sanity-check inputs --- caller should have got this right already + */ + Assert(PointerIsValid(arr)); + if (slice_ndim < 0 || slice_ndim > ARR_NDIM(arr)) + elog(ERROR, "invalid arguments to array_create_iterator"); + + /* + * Remember basic info about the array and its element type + */ + iterator->arr = arr; + iterator->nullbitmap = ARR_NULLBITMAP(arr); + iterator->nitems = ArrayGetNItems(ARR_NDIM(arr), ARR_DIMS(arr)); + get_typlenbyvalalign(ARR_ELEMTYPE(arr), + &iterator->typlen, + &iterator->typbyval, + &iterator->typalign); + + /* + * Remember the slicing parameters. + */ + iterator->slice_ndim = slice_ndim; + + if (slice_ndim > 0) + { + /* + * Get pointers into the array's dims and lbound arrays to represent + * the dims/lbound arrays of a slice. These are the same as the + * rightmost N dimensions of the array. + */ + iterator->slice_dims = ARR_DIMS(arr) + ARR_NDIM(arr) - slice_ndim; + iterator->slice_lbound = ARR_LBOUND(arr) + ARR_NDIM(arr) - slice_ndim; + + /* + * Compute number of elements in a slice. + */ + iterator->slice_len = ArrayGetNItems(slice_ndim, + iterator->slice_dims); + + /* + * Create workspace for building sub-arrays. + */ + iterator->slice_values = (Datum *) + palloc(iterator->slice_len * sizeof(Datum)); + iterator->slice_nulls = (bool *) + palloc(iterator->slice_len * sizeof(bool)); + } + + /* + * Initialize our data pointer and linear element number. These will + * advance through the array during array_iterate(). + */ + iterator->data_ptr = ARR_DATA_PTR(arr); + iterator->current_item = 0; + + return iterator; +} + +/* + * Iterate through the array referenced by 'iterator'. + * + * As long as there is another element (or slice), return it into + * *value / *isnull, and return true. Return false when no more data. + */ +bool +array_iterate(ArrayIterator iterator, Datum *value, bool *isnull) +{ + /* Done if we have reached the end of the array */ + if (iterator->current_item >= iterator->nitems) + return false; + + if (iterator->slice_ndim == 0) + { + /* + * Scalar case: return one element. + */ + if (array_get_isnull(iterator->nullbitmap, iterator->current_item++)) + { + *isnull = true; + *value = (Datum) 0; + } + else + { + /* non-NULL, so fetch the individual Datum to return */ + char *p = iterator->data_ptr; + + *isnull = false; + *value = fetch_att(p, iterator->typbyval, iterator->typlen); + + /* Move our data pointer forward to the next element */ + p = att_addlength_pointer(p, iterator->typlen, p); + p = (char *) att_align_nominal(p, iterator->typalign); + iterator->data_ptr = p; + } + } + else + { + /* + * Slice case: build and return an array of the requested size. + */ + ArrayType *result; + Datum *values = iterator->slice_values; + bool *nulls = iterator->slice_nulls; + char *p = iterator->data_ptr; + int i; + + for (i = 0; i < iterator->slice_len; i++) + { + if (array_get_isnull(iterator->nullbitmap, + iterator->current_item++)) + { + nulls[i] = true; + values[i] = (Datum) 0; + } + else + { + nulls[i] = false; + values[i] = fetch_att(p, iterator->typbyval, iterator->typlen); + + /* Move our data pointer forward to the next element */ + p = att_addlength_pointer(p, iterator->typlen, p); + p = (char *) att_align_nominal(p, iterator->typalign); + } + } + + iterator->data_ptr = p; + + result = construct_md_array(values, + nulls, + iterator->slice_ndim, + iterator->slice_dims, + iterator->slice_lbound, + ARR_ELEMTYPE(iterator->arr), + iterator->typlen, + iterator->typbyval, + iterator->typalign); + + *isnull = false; + *value = PointerGetDatum(result); + } + + return true; +} + +/* + * Release an ArrayIterator data structure + */ +void +array_free_iterator(ArrayIterator iterator) +{ + if (iterator->slice_ndim > 0) + { + pfree(iterator->slice_values); + pfree(iterator->slice_nulls); + } + pfree(iterator); +} + + /***************************************************************************/ /******************| Support Routines |*****************/ /***************************************************************************/ diff --git a/src/include/utils/array.h b/src/include/utils/array.h index 7f7e744cb1..6bc280f142 100644 --- a/src/include/utils/array.h +++ b/src/include/utils/array.h @@ -114,6 +114,9 @@ typedef struct ArrayMapState ArrayMetaState ret_extra; } ArrayMapState; +/* ArrayIteratorData is private in arrayfuncs.c */ +typedef struct ArrayIteratorData *ArrayIterator; + /* * fmgr macros for array objects */ @@ -254,6 +257,10 @@ extern Datum makeArrayResult(ArrayBuildState *astate, extern Datum makeMdArrayResult(ArrayBuildState *astate, int ndims, int *dims, int *lbs, MemoryContext rcontext, bool release); +extern ArrayIterator array_create_iterator(ArrayType *arr, int slice_ndim); +extern bool array_iterate(ArrayIterator iterator, Datum *value, bool *isnull); +extern void array_free_iterator(ArrayIterator iterator); + /* * prototypes for functions defined in arrayutils.c */ diff --git a/src/pl/plpgsql/src/gram.y b/src/pl/plpgsql/src/gram.y index eae9bbad6c..0ef6b5d48c 100644 --- a/src/pl/plpgsql/src/gram.y +++ b/src/pl/plpgsql/src/gram.y @@ -175,7 +175,7 @@ static List *read_raise_options(void); %type expr_until_then expr_until_loop opt_expr_until_when %type opt_exitcond -%type assign_var +%type assign_var foreach_slice %type cursor_variable %type decl_cursor_arg %type for_variable @@ -190,7 +190,7 @@ static List *read_raise_options(void); %type stmt_return stmt_raise stmt_execsql %type stmt_dynexecute stmt_for stmt_perform stmt_getdiag %type stmt_open stmt_fetch stmt_move stmt_close stmt_null -%type stmt_case +%type stmt_case stmt_foreach_a %type proc_exceptions %type exception_sect @@ -239,6 +239,7 @@ static List *read_raise_options(void); %token K_ABSOLUTE %token K_ALIAS %token K_ALL +%token K_ARRAY %token K_BACKWARD %token K_BEGIN %token K_BY @@ -264,6 +265,7 @@ static List *read_raise_options(void); %token K_FETCH %token K_FIRST %token K_FOR +%token K_FOREACH %token K_FORWARD %token K_FROM %token K_GET @@ -298,6 +300,7 @@ static List *read_raise_options(void); %token K_ROWTYPE %token K_ROW_COUNT %token K_SCROLL +%token K_SLICE %token K_SQLSTATE %token K_STRICT %token K_THEN @@ -739,6 +742,8 @@ proc_stmt : pl_block ';' { $$ = $1; } | stmt_for { $$ = $1; } + | stmt_foreach_a + { $$ = $1; } | stmt_exit { $$ = $1; } | stmt_return @@ -1386,6 +1391,58 @@ for_variable : T_DATUM } ; +stmt_foreach_a : opt_block_label K_FOREACH for_variable foreach_slice K_IN K_ARRAY expr_until_loop loop_body + { + PLpgSQL_stmt_foreach_a *new; + + new = palloc0(sizeof(PLpgSQL_stmt_foreach_a)); + new->cmd_type = PLPGSQL_STMT_FOREACH_A; + new->lineno = plpgsql_location_to_lineno(@2); + new->label = $1; + new->slice = $4; + new->expr = $7; + new->body = $8.stmts; + + if ($3.rec) + { + new->varno = $3.rec->dno; + check_assignable((PLpgSQL_datum *) $3.rec, @3); + } + else if ($3.row) + { + new->varno = $3.row->dno; + check_assignable((PLpgSQL_datum *) $3.row, @3); + } + else if ($3.scalar) + { + new->varno = $3.scalar->dno; + check_assignable($3.scalar, @3); + } + else + { + ereport(ERROR, + (errcode(ERRCODE_SYNTAX_ERROR), + errmsg("loop variable of FOREACH must be a known variable or list of variables"), + parser_errposition(@3))); + } + + check_labels($1, $8.end_label, $8.end_label_location); + plpgsql_ns_pop(); + + $$ = (PLpgSQL_stmt *) new; + } + ; + +foreach_slice : + { + $$ = 0; + } + | K_SLICE ICONST + { + $$ = $2; + } + ; + stmt_exit : exit_type opt_label opt_exitcond { PLpgSQL_stmt_exit *new; @@ -2035,6 +2092,7 @@ any_identifier : T_WORD unreserved_keyword : K_ABSOLUTE | K_ALIAS + | K_ARRAY | K_BACKWARD | K_CONSTANT | K_CURSOR @@ -2063,6 +2121,7 @@ unreserved_keyword : | K_ROW_COUNT | K_ROWTYPE | K_SCROLL + | K_SLICE | K_SQLSTATE | K_TYPE | K_USE_COLUMN diff --git a/src/pl/plpgsql/src/pl_exec.c b/src/pl/plpgsql/src/pl_exec.c index b685841d97..7af6eee088 100644 --- a/src/pl/plpgsql/src/pl_exec.c +++ b/src/pl/plpgsql/src/pl_exec.c @@ -107,6 +107,8 @@ static int exec_stmt_fors(PLpgSQL_execstate *estate, PLpgSQL_stmt_fors *stmt); static int exec_stmt_forc(PLpgSQL_execstate *estate, PLpgSQL_stmt_forc *stmt); +static int exec_stmt_foreach_a(PLpgSQL_execstate *estate, + PLpgSQL_stmt_foreach_a *stmt); static int exec_stmt_open(PLpgSQL_execstate *estate, PLpgSQL_stmt_open *stmt); static int exec_stmt_fetch(PLpgSQL_execstate *estate, @@ -1312,6 +1314,10 @@ exec_stmt(PLpgSQL_execstate *estate, PLpgSQL_stmt *stmt) rc = exec_stmt_forc(estate, (PLpgSQL_stmt_forc *) stmt); break; + case PLPGSQL_STMT_FOREACH_A: + rc = exec_stmt_foreach_a(estate, (PLpgSQL_stmt_foreach_a *) stmt); + break; + case PLPGSQL_STMT_EXIT: rc = exec_stmt_exit(estate, (PLpgSQL_stmt_exit *) stmt); break; @@ -2027,6 +2033,185 @@ exec_stmt_forc(PLpgSQL_execstate *estate, PLpgSQL_stmt_forc *stmt) } +/* ---------- + * exec_stmt_foreach_a Loop over elements or slices of an array + * + * When looping over elements, the loop variable is the same type that the + * array stores (eg: integer), when looping through slices, the loop variable + * is an array of size and dimensions to match the size of the slice. + * ---------- + */ +static int +exec_stmt_foreach_a(PLpgSQL_execstate *estate, PLpgSQL_stmt_foreach_a *stmt) +{ + ArrayType *arr; + Oid arrtype; + PLpgSQL_datum *loop_var; + Oid loop_var_elem_type; + bool found = false; + int rc = PLPGSQL_RC_OK; + ArrayIterator array_iterator; + Oid iterator_result_type; + Datum value; + bool isnull; + + /* get the value of the array expression */ + value = exec_eval_expr(estate, stmt->expr, &isnull, &arrtype); + if (isnull) + ereport(ERROR, + (errcode(ERRCODE_NULL_VALUE_NOT_ALLOWED), + errmsg("FOREACH expression must not be NULL"))); + + /* check the type of the expression - must be an array */ + if (!OidIsValid(get_element_type(arrtype))) + ereport(ERROR, + (errcode(ERRCODE_DATATYPE_MISMATCH), + errmsg("FOREACH expression must yield an array, not type %s", + format_type_be(arrtype)))); + + /* + * We must copy the array, else it will disappear in exec_eval_cleanup. + * This is annoying, but cleanup will certainly happen while running the + * loop body, so we have little choice. + */ + arr = DatumGetArrayTypePCopy(value); + + /* Clean up any leftover temporary memory */ + exec_eval_cleanup(estate); + + /* Slice dimension must be less than or equal to array dimension */ + if (stmt->slice < 0 || stmt->slice > ARR_NDIM(arr)) + ereport(ERROR, + (errcode(ERRCODE_ARRAY_SUBSCRIPT_ERROR), + errmsg("slice dimension (%d) is out of the valid range 0..%d", + stmt->slice, ARR_NDIM(arr)))); + + /* Set up the loop variable and see if it is of an array type */ + loop_var = estate->datums[stmt->varno]; + if (loop_var->dtype == PLPGSQL_DTYPE_REC || + loop_var->dtype == PLPGSQL_DTYPE_ROW) + { + /* + * Record/row variable is certainly not of array type, and might not + * be initialized at all yet, so don't try to get its type + */ + loop_var_elem_type = InvalidOid; + } + else + loop_var_elem_type = get_element_type(exec_get_datum_type(estate, + loop_var)); + + /* + * Sanity-check the loop variable type. We don't try very hard here, + * and should not be too picky since it's possible that exec_assign_value + * can coerce values of different types. But it seems worthwhile to + * complain if the array-ness of the loop variable is not right. + */ + if (stmt->slice > 0 && loop_var_elem_type == InvalidOid) + ereport(ERROR, + (errcode(ERRCODE_DATATYPE_MISMATCH), + errmsg("FOREACH ... SLICE loop variable must be of an array type"))); + if (stmt->slice == 0 && loop_var_elem_type != InvalidOid) + ereport(ERROR, + (errcode(ERRCODE_DATATYPE_MISMATCH), + errmsg("FOREACH loop variable must not be of an array type"))); + + /* Create an iterator to step through the array */ + array_iterator = array_create_iterator(arr, stmt->slice); + + /* Identify iterator result type */ + if (stmt->slice > 0) + { + /* When slicing, nominal type of result is same as array type */ + iterator_result_type = arrtype; + } + else + { + /* Without slicing, results are individual array elements */ + iterator_result_type = ARR_ELEMTYPE(arr); + } + + /* Iterate over the array elements or slices */ + while (array_iterate(array_iterator, &value, &isnull)) + { + found = true; /* looped at least once */ + + /* Assign current element/slice to the loop variable */ + exec_assign_value(estate, loop_var, value, iterator_result_type, + &isnull); + + /* In slice case, value is temporary; must free it to avoid leakage */ + if (stmt->slice > 0) + pfree(DatumGetPointer(value)); + + /* + * Execute the statements + */ + rc = exec_stmts(estate, stmt->body); + + /* Handle the return code */ + if (rc == PLPGSQL_RC_RETURN) + break; /* break out of the loop */ + else if (rc == PLPGSQL_RC_EXIT) + { + if (estate->exitlabel == NULL) + /* unlabelled exit, finish the current loop */ + rc = PLPGSQL_RC_OK; + else if (stmt->label != NULL && + strcmp(stmt->label, estate->exitlabel) == 0) + { + /* labelled exit, matches the current stmt's label */ + estate->exitlabel = NULL; + rc = PLPGSQL_RC_OK; + } + + /* + * otherwise, this is a labelled exit that does not match the + * current statement's label, if any: return RC_EXIT so that the + * EXIT continues to propagate up the stack. + */ + break; + } + else if (rc == PLPGSQL_RC_CONTINUE) + { + if (estate->exitlabel == NULL) + /* unlabelled continue, so re-run the current loop */ + rc = PLPGSQL_RC_OK; + else if (stmt->label != NULL && + strcmp(stmt->label, estate->exitlabel) == 0) + { + /* label matches named continue, so re-run loop */ + estate->exitlabel = NULL; + rc = PLPGSQL_RC_OK; + } + else + { + /* + * otherwise, this is a named continue that does not match the + * current statement's label, if any: return RC_CONTINUE so + * that the CONTINUE will propagate up the stack. + */ + break; + } + } + } + + /* Release temporary memory, including the array value */ + array_free_iterator(array_iterator); + pfree(arr); + + /* + * 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; +} + + /* ---------- * exec_stmt_exit Implements EXIT and CONTINUE * diff --git a/src/pl/plpgsql/src/pl_funcs.c b/src/pl/plpgsql/src/pl_funcs.c index e24f71ac6c..f13e4c3db6 100644 --- a/src/pl/plpgsql/src/pl_funcs.c +++ b/src/pl/plpgsql/src/pl_funcs.c @@ -230,6 +230,8 @@ plpgsql_stmt_typename(PLpgSQL_stmt *stmt) return _("FOR over SELECT rows"); case PLPGSQL_STMT_FORC: return _("FOR over cursor"); + case PLPGSQL_STMT_FOREACH_A: + return _("FOREACH over array"); case PLPGSQL_STMT_EXIT: return "EXIT"; case PLPGSQL_STMT_RETURN: @@ -278,6 +280,7 @@ static void dump_while(PLpgSQL_stmt_while *stmt); static void dump_fori(PLpgSQL_stmt_fori *stmt); static void dump_fors(PLpgSQL_stmt_fors *stmt); static void dump_forc(PLpgSQL_stmt_forc *stmt); +static void dump_foreach_a(PLpgSQL_stmt_foreach_a *stmt); static void dump_exit(PLpgSQL_stmt_exit *stmt); static void dump_return(PLpgSQL_stmt_return *stmt); static void dump_return_next(PLpgSQL_stmt_return_next *stmt); @@ -337,6 +340,9 @@ dump_stmt(PLpgSQL_stmt *stmt) case PLPGSQL_STMT_FORC: dump_forc((PLpgSQL_stmt_forc *) stmt); break; + case PLPGSQL_STMT_FOREACH_A: + dump_foreach_a((PLpgSQL_stmt_foreach_a *) stmt); + break; case PLPGSQL_STMT_EXIT: dump_exit((PLpgSQL_stmt_exit *) stmt); break; @@ -595,6 +601,23 @@ dump_forc(PLpgSQL_stmt_forc *stmt) printf(" ENDFORC\n"); } +static void +dump_foreach_a(PLpgSQL_stmt_foreach_a *stmt) +{ + dump_ind(); + printf("FOREACHA var %d ", stmt->varno); + if (stmt->slice != 0) + printf("SLICE %d ", stmt->slice); + printf("IN "); + dump_expr(stmt->expr); + printf("\n"); + + dump_stmts(stmt->body); + + dump_ind(); + printf(" ENDFOREACHA"); +} + static void dump_open(PLpgSQL_stmt_open *stmt) { diff --git a/src/pl/plpgsql/src/pl_scanner.c b/src/pl/plpgsql/src/pl_scanner.c index 6675184d61..e8a2628f2f 100644 --- a/src/pl/plpgsql/src/pl_scanner.c +++ b/src/pl/plpgsql/src/pl_scanner.c @@ -77,6 +77,7 @@ static const ScanKeyword reserved_keywords[] = { PG_KEYWORD("exit", K_EXIT, RESERVED_KEYWORD) PG_KEYWORD("fetch", K_FETCH, RESERVED_KEYWORD) PG_KEYWORD("for", K_FOR, RESERVED_KEYWORD) + PG_KEYWORD("foreach", K_FOREACH, RESERVED_KEYWORD) PG_KEYWORD("from", K_FROM, RESERVED_KEYWORD) PG_KEYWORD("get", K_GET, RESERVED_KEYWORD) PG_KEYWORD("if", K_IF, RESERVED_KEYWORD) @@ -105,6 +106,7 @@ static const int num_reserved_keywords = lengthof(reserved_keywords); static const ScanKeyword unreserved_keywords[] = { PG_KEYWORD("absolute", K_ABSOLUTE, UNRESERVED_KEYWORD) PG_KEYWORD("alias", K_ALIAS, UNRESERVED_KEYWORD) + PG_KEYWORD("array", K_ARRAY, UNRESERVED_KEYWORD) PG_KEYWORD("backward", K_BACKWARD, UNRESERVED_KEYWORD) PG_KEYWORD("constant", K_CONSTANT, UNRESERVED_KEYWORD) PG_KEYWORD("cursor", K_CURSOR, UNRESERVED_KEYWORD) @@ -133,6 +135,7 @@ static const ScanKeyword unreserved_keywords[] = { PG_KEYWORD("row_count", K_ROW_COUNT, UNRESERVED_KEYWORD) PG_KEYWORD("rowtype", K_ROWTYPE, UNRESERVED_KEYWORD) PG_KEYWORD("scroll", K_SCROLL, UNRESERVED_KEYWORD) + PG_KEYWORD("slice", K_SLICE, UNRESERVED_KEYWORD) PG_KEYWORD("sqlstate", K_SQLSTATE, UNRESERVED_KEYWORD) PG_KEYWORD("type", K_TYPE, UNRESERVED_KEYWORD) PG_KEYWORD("use_column", K_USE_COLUMN, UNRESERVED_KEYWORD) diff --git a/src/pl/plpgsql/src/plpgsql.h b/src/pl/plpgsql/src/plpgsql.h index 0ad7e28136..7015379842 100644 --- a/src/pl/plpgsql/src/plpgsql.h +++ b/src/pl/plpgsql/src/plpgsql.h @@ -90,6 +90,7 @@ enum PLpgSQL_stmt_types PLPGSQL_STMT_FORI, PLPGSQL_STMT_FORS, PLPGSQL_STMT_FORC, + PLPGSQL_STMT_FOREACH_A, PLPGSQL_STMT_EXIT, PLPGSQL_STMT_RETURN, PLPGSQL_STMT_RETURN_NEXT, @@ -494,6 +495,18 @@ typedef struct } PLpgSQL_stmt_dynfors; +typedef struct +{ /* FOREACH item in array loop */ + int cmd_type; + int lineno; + char *label; + int varno; /* loop target variable */ + int slice; /* slice dimension, or 0 */ + PLpgSQL_expr *expr; /* array expression */ + List *body; /* List of statements */ +} PLpgSQL_stmt_foreach_a; + + typedef struct { /* OPEN a curvar */ int cmd_type; diff --git a/src/test/regress/expected/plpgsql.out b/src/test/regress/expected/plpgsql.out index 22ccce212c..bfabcbc8b4 100644 --- a/src/test/regress/expected/plpgsql.out +++ b/src/test/regress/expected/plpgsql.out @@ -4240,3 +4240,197 @@ select unreserved_test(); (1 row) drop function unreserved_test(); +-- +-- Test FOREACH over arrays +-- +create function foreach_test(anyarray) +returns void as $$ +declare x int; +begin + foreach x in array $1 + loop + raise notice '%', x; + end loop; + end; +$$ language plpgsql; +select foreach_test(ARRAY[1,2,3,4]); +NOTICE: 1 +NOTICE: 2 +NOTICE: 3 +NOTICE: 4 + foreach_test +-------------- + +(1 row) + +select foreach_test(ARRAY[[1,2],[3,4]]); +NOTICE: 1 +NOTICE: 2 +NOTICE: 3 +NOTICE: 4 + foreach_test +-------------- + +(1 row) + +create or replace function foreach_test(anyarray) +returns void as $$ +declare x int; +begin + foreach x slice 1 in array $1 + loop + raise notice '%', x; + end loop; + end; +$$ language plpgsql; +-- should fail +select foreach_test(ARRAY[1,2,3,4]); +ERROR: FOREACH ... SLICE loop variable must be of an array type +CONTEXT: PL/pgSQL function "foreach_test" line 4 at FOREACH over array +select foreach_test(ARRAY[[1,2],[3,4]]); +ERROR: FOREACH ... SLICE loop variable must be of an array type +CONTEXT: PL/pgSQL function "foreach_test" line 4 at FOREACH over array +create or replace function foreach_test(anyarray) +returns void as $$ +declare x int[]; +begin + foreach x slice 1 in array $1 + loop + raise notice '%', x; + end loop; + end; +$$ language plpgsql; +select foreach_test(ARRAY[1,2,3,4]); +NOTICE: {1,2,3,4} + foreach_test +-------------- + +(1 row) + +select foreach_test(ARRAY[[1,2],[3,4]]); +NOTICE: {1,2} +NOTICE: {3,4} + foreach_test +-------------- + +(1 row) + +-- higher level of slicing +create or replace function foreach_test(anyarray) +returns void as $$ +declare x int[]; +begin + foreach x slice 2 in array $1 + loop + raise notice '%', x; + end loop; + end; +$$ language plpgsql; +-- should fail +select foreach_test(ARRAY[1,2,3,4]); +ERROR: slice dimension (2) is out of the valid range 0..1 +CONTEXT: PL/pgSQL function "foreach_test" line 4 at FOREACH over array +-- ok +select foreach_test(ARRAY[[1,2],[3,4]]); +NOTICE: {{1,2},{3,4}} + foreach_test +-------------- + +(1 row) + +select foreach_test(ARRAY[[[1,2]],[[3,4]]]); +NOTICE: {{1,2}} +NOTICE: {{3,4}} + foreach_test +-------------- + +(1 row) + +create type xy_tuple AS (x int, y int); +-- iteration over array of records +create or replace function foreach_test(anyarray) +returns void as $$ +declare r record; +begin + foreach r in array $1 + loop + raise notice '%', r; + end loop; + end; +$$ language plpgsql; +select foreach_test(ARRAY[(10,20),(40,69),(35,78)]::xy_tuple[]); +NOTICE: (10,20) +NOTICE: (40,69) +NOTICE: (35,78) + foreach_test +-------------- + +(1 row) + +select foreach_test(ARRAY[[(10,20),(40,69)],[(35,78),(88,76)]]::xy_tuple[]); +NOTICE: (10,20) +NOTICE: (40,69) +NOTICE: (35,78) +NOTICE: (88,76) + foreach_test +-------------- + +(1 row) + +create or replace function foreach_test(anyarray) +returns void as $$ +declare x int; y int; +begin + foreach x, y in array $1 + loop + raise notice 'x = %, y = %', x, y; + end loop; + end; +$$ language plpgsql; +select foreach_test(ARRAY[(10,20),(40,69),(35,78)]::xy_tuple[]); +NOTICE: x = 10, y = 20 +NOTICE: x = 40, y = 69 +NOTICE: x = 35, y = 78 + foreach_test +-------------- + +(1 row) + +select foreach_test(ARRAY[[(10,20),(40,69)],[(35,78),(88,76)]]::xy_tuple[]); +NOTICE: x = 10, y = 20 +NOTICE: x = 40, y = 69 +NOTICE: x = 35, y = 78 +NOTICE: x = 88, y = 76 + foreach_test +-------------- + +(1 row) + +-- slicing over array of composite types +create or replace function foreach_test(anyarray) +returns void as $$ +declare x xy_tuple[]; +begin + foreach x slice 1 in array $1 + loop + raise notice '%', x; + end loop; + end; +$$ language plpgsql; +select foreach_test(ARRAY[(10,20),(40,69),(35,78)]::xy_tuple[]); +NOTICE: {"(10,20)","(40,69)","(35,78)"} + foreach_test +-------------- + +(1 row) + +select foreach_test(ARRAY[[(10,20),(40,69)],[(35,78),(88,76)]]::xy_tuple[]); +NOTICE: {"(10,20)","(40,69)"} +NOTICE: {"(35,78)","(88,76)"} + foreach_test +-------------- + +(1 row) + +drop function foreach_test(anyarray); +drop type xy_tuple; diff --git a/src/test/regress/sql/plpgsql.sql b/src/test/regress/sql/plpgsql.sql index d0f4e3b5e1..14fb4578c6 100644 --- a/src/test/regress/sql/plpgsql.sql +++ b/src/test/regress/sql/plpgsql.sql @@ -3375,3 +3375,117 @@ $$ language plpgsql; select unreserved_test(); drop function unreserved_test(); + +-- +-- Test FOREACH over arrays +-- + +create function foreach_test(anyarray) +returns void as $$ +declare x int; +begin + foreach x in array $1 + loop + raise notice '%', x; + end loop; + end; +$$ language plpgsql; + +select foreach_test(ARRAY[1,2,3,4]); +select foreach_test(ARRAY[[1,2],[3,4]]); + +create or replace function foreach_test(anyarray) +returns void as $$ +declare x int; +begin + foreach x slice 1 in array $1 + loop + raise notice '%', x; + end loop; + end; +$$ language plpgsql; + +-- should fail +select foreach_test(ARRAY[1,2,3,4]); +select foreach_test(ARRAY[[1,2],[3,4]]); + +create or replace function foreach_test(anyarray) +returns void as $$ +declare x int[]; +begin + foreach x slice 1 in array $1 + loop + raise notice '%', x; + end loop; + end; +$$ language plpgsql; + +select foreach_test(ARRAY[1,2,3,4]); +select foreach_test(ARRAY[[1,2],[3,4]]); + +-- higher level of slicing +create or replace function foreach_test(anyarray) +returns void as $$ +declare x int[]; +begin + foreach x slice 2 in array $1 + loop + raise notice '%', x; + end loop; + end; +$$ language plpgsql; + +-- should fail +select foreach_test(ARRAY[1,2,3,4]); +-- ok +select foreach_test(ARRAY[[1,2],[3,4]]); +select foreach_test(ARRAY[[[1,2]],[[3,4]]]); + +create type xy_tuple AS (x int, y int); + +-- iteration over array of records +create or replace function foreach_test(anyarray) +returns void as $$ +declare r record; +begin + foreach r in array $1 + loop + raise notice '%', r; + end loop; + end; +$$ language plpgsql; + +select foreach_test(ARRAY[(10,20),(40,69),(35,78)]::xy_tuple[]); +select foreach_test(ARRAY[[(10,20),(40,69)],[(35,78),(88,76)]]::xy_tuple[]); + +create or replace function foreach_test(anyarray) +returns void as $$ +declare x int; y int; +begin + foreach x, y in array $1 + loop + raise notice 'x = %, y = %', x, y; + end loop; + end; +$$ language plpgsql; + +select foreach_test(ARRAY[(10,20),(40,69),(35,78)]::xy_tuple[]); +select foreach_test(ARRAY[[(10,20),(40,69)],[(35,78),(88,76)]]::xy_tuple[]); + +-- slicing over array of composite types +create or replace function foreach_test(anyarray) +returns void as $$ +declare x xy_tuple[]; +begin + foreach x slice 1 in array $1 + loop + raise notice '%', x; + end loop; + end; +$$ language plpgsql; + +select foreach_test(ARRAY[(10,20),(40,69),(35,78)]::xy_tuple[]); +select foreach_test(ARRAY[[(10,20),(40,69)],[(35,78),(88,76)]]::xy_tuple[]); + +drop function foreach_test(anyarray); +drop type xy_tuple; -- 2.40.0