diff options
| -rw-r--r-- | doc/src/sgml/plpgsql.sgml | 106 | ||||
| -rw-r--r-- | src/backend/utils/adt/arrayfuncs.c | 206 | ||||
| -rw-r--r-- | src/include/utils/array.h | 7 | ||||
| -rw-r--r-- | src/pl/plpgsql/src/gram.y | 63 | ||||
| -rw-r--r-- | src/pl/plpgsql/src/pl_exec.c | 185 | ||||
| -rw-r--r-- | src/pl/plpgsql/src/pl_funcs.c | 23 | ||||
| -rw-r--r-- | src/pl/plpgsql/src/pl_scanner.c | 3 | ||||
| -rw-r--r-- | src/pl/plpgsql/src/plpgsql.h | 13 | ||||
| -rw-r--r-- | src/test/regress/expected/plpgsql.out | 194 | ||||
| -rw-r--r-- | 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 a2601e6bc89..c342916ff36 100644 --- a/doc/src/sgml/plpgsql.sgml +++ b/doc/src/sgml/plpgsql.sgml @@ -1263,7 +1263,7 @@ EXECUTE 'UPDATE tbl SET ' <programlisting> EXECUTE format('UPDATE tbl SET %I = %L WHERE key = %L', colname, newvalue, keyvalue); </programlisting> - The <function>format</function> function can be used in conjunction with + The <function>format</function> function can be used in conjunction with the <literal>USING</literal> clause: <programlisting> 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. </para> </listitem> - <listitem> <para> - A <command>FOR</> statement sets <literal>FOUND</literal> true - if it iterates one or more times, else false. This applies to - all four variants of the <command>FOR</> statement (integer - <command>FOR</> loops, record-set <command>FOR</> loops, - dynamic record-set <command>FOR</> loops, and cursor - <command>FOR</> loops). + A <command>FOR</> or <command>FOREACH</> statement sets + <literal>FOUND</literal> true + if it iterates one or more times, else false. <literal>FOUND</literal> is set this way when the - <command>FOR</> loop exits; inside the execution of the loop, + loop exits; inside the execution of the loop, <literal>FOUND</literal> is not modified by the - <command>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. </para> </listitem> @@ -1910,9 +1906,9 @@ END CASE; <para> With the <literal>LOOP</>, <literal>EXIT</>, - <literal>CONTINUE</>, <literal>WHILE</>, and <literal>FOR</> - statements, you can arrange for your <application>PL/pgSQL</> - function to repeat a series of commands. + <literal>CONTINUE</>, <literal>WHILE</>, <literal>FOR</>, + and <literal>FOREACH</> statements, you can arrange for your + <application>PL/pgSQL</> function to repeat a series of commands. </para> <sect3> @@ -2238,6 +2234,90 @@ END LOOP <optional> <replaceable>label</replaceable> </optional>; </para> </sect2> + <sect2 id="plpgsql-foreach-array"> + <title>Looping Through Arrays</title> + + <para> + The <literal>FOREACH</> loop is much like a <literal>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, <literal>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 <literal>FOREACH</> statement to loop over an array is: + +<synopsis> +<optional> <<<replaceable>label</replaceable>>> </optional> +FOREACH <replaceable>target</replaceable> <optional> SLICE <replaceable>number</replaceable> </optional> IN ARRAY <replaceable>expression</replaceable> LOOP + <replaceable>statements</replaceable> +END LOOP <optional> <replaceable>label</replaceable> </optional>; +</synopsis> + </para> + + <para> + Without <literal>SLICE</>, or if <literal>SLICE 0</> is specified, + the loop iterates through individual elements of the array produced + by evaluating the <replaceable>expression</replaceable>. + The <replaceable>target</replaceable> 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: + +<programlisting> +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; +</programlisting> + + The elements are visited in storage order, regardless of the number of + array dimensions. Although the <replaceable>target</replaceable> 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. + </para> + + <para> + With a positive <literal>SLICE</> value, <literal>FOREACH</> + iterates through slices of the array rather than single elements. + The <literal>SLICE</> value must be an integer constant not larger + than the number of dimensions of the array. The + <replaceable>target</replaceable> 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 <literal>SLICE</>. + Here is an example of iterating through one-dimensional slices: + +<programlisting> +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} +</programlisting> + </para> + </sect2> + <sect2 id="plpgsql-error-trapping"> <title>Trapping Errors</title> diff --git a/src/backend/utils/adt/arrayfuncs.c b/src/backend/utils/adt/arrayfuncs.c index 4ac98308789..e023b2458ed 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 7f7e744cb12..6bc280f1424 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 eae9bbad6c7..0ef6b5d48c3 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> expr_until_then expr_until_loop opt_expr_until_when %type <expr> opt_exitcond -%type <ival> assign_var +%type <ival> assign_var foreach_slice %type <var> cursor_variable %type <datum> decl_cursor_arg %type <forvariable> for_variable @@ -190,7 +190,7 @@ static List *read_raise_options(void); %type <stmt> stmt_return stmt_raise stmt_execsql %type <stmt> stmt_dynexecute stmt_for stmt_perform stmt_getdiag %type <stmt> stmt_open stmt_fetch stmt_move stmt_close stmt_null -%type <stmt> stmt_case +%type <stmt> stmt_case stmt_foreach_a %type <list> proc_exceptions %type <exception_block> exception_sect @@ -239,6 +239,7 @@ static List *read_raise_options(void); %token <keyword> K_ABSOLUTE %token <keyword> K_ALIAS %token <keyword> K_ALL +%token <keyword> K_ARRAY %token <keyword> K_BACKWARD %token <keyword> K_BEGIN %token <keyword> K_BY @@ -264,6 +265,7 @@ static List *read_raise_options(void); %token <keyword> K_FETCH %token <keyword> K_FIRST %token <keyword> K_FOR +%token <keyword> K_FOREACH %token <keyword> K_FORWARD %token <keyword> K_FROM %token <keyword> K_GET @@ -298,6 +300,7 @@ static List *read_raise_options(void); %token <keyword> K_ROWTYPE %token <keyword> K_ROW_COUNT %token <keyword> K_SCROLL +%token <keyword> K_SLICE %token <keyword> K_SQLSTATE %token <keyword> K_STRICT %token <keyword> 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 b685841d971..7af6eee088e 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; @@ -2028,6 +2034,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 * * This begins the process of exiting / restarting a loop. diff --git a/src/pl/plpgsql/src/pl_funcs.c b/src/pl/plpgsql/src/pl_funcs.c index e24f71ac6c7..f13e4c3db63 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; @@ -596,6 +602,23 @@ dump_forc(PLpgSQL_stmt_forc *stmt) } 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) { dump_ind(); diff --git a/src/pl/plpgsql/src/pl_scanner.c b/src/pl/plpgsql/src/pl_scanner.c index 6675184d613..e8a2628f2f1 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 0ad7e28136b..7015379842c 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, @@ -495,6 +496,18 @@ typedef struct 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; int lineno; diff --git a/src/test/regress/expected/plpgsql.out b/src/test/regress/expected/plpgsql.out index 22ccce212c4..bfabcbc8b44 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 d0f4e3b5e1f..14fb4578c60 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; |