Greetings, * Pavel Stehule (pavel.steh...@gmail.com) wrote: > I resend a patch with last update of this patch
Alright, so, like I said, I really like this feature and would like to
see it included. To that end, I've done perhaps a bit more than a
review of the patch. Pavel, if you could go over the changes I've made
and review them and let me know if you see any problems, I'd appreciate
it. I've tried to get it ready for a committer as much as I can without
being one. :)
I moved the array iteration over into arrayfuncs.c, cleaned it up quite
a bit, cleaned up the pl/pgsql foreach function, improved the PL/PgSQL
documentation to understand FOREACH as another top-level command, added
comments all over the place, etc.
Passes all regressions too.
commit 19deaf69a4dabfa4a223a6dcd36570866ad0bd3c
Author: Stephen Frost <sfr...@snowman.net>
Date: Tue Feb 8 15:15:48 2011 -0500
PL/PgSQL FOREACH cleanup
Define and rename element OID to be more consistant, ensure
that the right name gets returned in error messages, and fix
regression output to match new error message (grammar cleanup).
commit f88fd2ab5419f9a2784677038b3fb01053c69163
Merge: f191af1 8c6e3ad
Author: Stephen Frost <sfr...@snowman.net>
Date: Tue Feb 8 14:28:18 2011 -0500
Merge branch 'master' of git://git.postgresql.org/git/postgresql into
plpgsql_foreach
commit f191af16f9d3e5ae0072e61c1b58713040cc8d64
Author: Stephen Frost <sfr...@snowman.net>
Date: Tue Feb 8 14:27:05 2011 -0500
PL/PgSQL FOREACH Minor Whitespace Cleanup
commit 612cf5485f202a49aec70cf32f74d19d0d130b6b
Author: Stephen Frost <sfr...@snowman.net>
Date: Tue Feb 8 14:06:06 2011 -0500
Improving FOREACH, code and documentation
This patch moves and reworks much of the array iteration code
that FOREACH had been implemented with to be part of arrayfuncs.c
and exported through utils/array.h. It also cleans up the error
handling and set up pieces of the FOREACH handling in pl_exec.c
Lastly, the documentation and comments are updated and improved.
commit 89058b79e43311e8f37af16c3fc17b622dc97578
Author: Stephen Frost <sfr...@snowman.net>
Date: Sun Feb 6 14:14:04 2011 -0500
Add FOREACH top-level PL/PgSQL command
This patch adds a new top-level PL/PgSQL command called FOREACH which
is intended to be for iterating over multi-value variables. This also
includes the first FOREACH type, an ARRAY iteration capability.
Patch by Pavel Stehule.
Thanks,
Stephen
*** a/doc/src/sgml/plpgsql.sgml
--- b/doc/src/sgml/plpgsql.sgml
***************
*** 300,310 **** $$ LANGUAGE plpgsql;
<para>
All variables used in a block must be declared in the
declarations section of the block.
! (The only exceptions are that the loop variable of a <literal>FOR</> loop
! iterating over a range of integer values is automatically declared as an
! integer variable, and likewise the loop variable of a <literal>FOR</> loop
! iterating over a cursor's result is automatically declared as a
! record variable.)
</para>
<para>
--- 300,308 ----
<para>
All variables used in a block must be declared in the
declarations section of the block.
! (The only exceptions are the loop variables of <literal>FOR</> and
! <literal>FOREACH</> loops which are automatically declared as the
! appropriate variable type to match what to loop is over.)
</para>
<para>
***************
*** 1359,1375 **** GET DIAGNOSTICS integer_var = ROW_COUNT;
<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).
! <literal>FOUND</literal> is set this way when the
! <command>FOR</> 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
! execution of other statements within the loop body.
</para>
</listitem>
<listitem>
--- 1357,1375 ----
<listitem>
<para>
! A <command>FOR</> or <command>FOREACH</> statement sets
! <literal>FOUND</literal> to true if it iterates one or more times,
! else to 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) and all variants of the
! <command>FOREACH</> statement (currently only ARRAY
! <command>FOREACH</> loops). <literal>FOUND</literal> is set this
! way when the <command>FOR</> or <command>FOREACH</> loop exits;
! inside the execution of the loop, <literal>FOUND</literal> is not
! modified by the <command>FOR</> or <command>FOREACH</> statement,
! although it might be changed by the execution of other statements
! within the loop body.
</para>
</listitem>
<listitem>
***************
*** 1910,1918 **** 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.
</para>
<sect3>
--- 1910,1918 ----
<para>
With the <literal>LOOP</>, <literal>EXIT</>,
! <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,2243 **** END LOOP <optional> <replaceable>label</replaceable> </optional>;
--- 2238,2285 ----
</para>
</sect2>
+ <sect2 id="plpgsql-array-foreach-loop">
+ <title>Looping Through Arrays</title>
+
+ <para>
+ Similar to a <literal>FOR</> loop is the <literal>FOREACH</> loop.
+ <literal>FOREACH</> is used to loop over multi-value variables, such
+ as ARRAYs. Other multi-value variables may be added to <literal>FOREACH</>
+ later. Note that <literal>FOREACH</> can be thought of horizantally
+ looping, whereas <literal>FOR</> can be thought of a vertical loop.
+ The <literal>FOREACH</> statement to loop over an ARRAY is:
+
+ <synopsis>
+ <optional> <<<replaceable>label</replaceable>>> </optional>
+ FOREACH <replaceable>target</replaceable> <optional> SCALE <replaceable>number</replaceable> </optional> IN <replaceable>expression</replaceable> LOOP
+ <replaceable>statements</replaceable>
+ END LOOP <optional> <replaceable>label</replaceable> </optional>;
+ </synopsis>
+
+ This <literal>FOREACH</> form allows looping over each variable in an
+ array, in which case the loop variable will be the array's component
+ type (eg: the single-value loop variable for an integer ARRAY would
+ be an integer), or looping over segments of the array, in which case
+ the loop variable will be an array itself of the same type as the array.
+ Following is an example of looping through an integer array:
+
+ <programlisting>
+ CREATE FUNCTION sum(VARIADIC int[]) RETURNS int8 AS $$
+ DECLARE
+ s int8; x int;
+ BEGIN
+ FOREACH x IN $1
+ LOOP
+ s := s + x;
+ END LOOP;
+ RETURN s;
+ END;
+ $$ LANGUAGE plpgsql;
+ </programlisting>
+ </para>
+
+ </sect2>
+
<sect2 id="plpgsql-error-trapping">
<title>Trapping Errors</title>
*** a/src/backend/utils/adt/arrayfuncs.c
--- b/src/backend/utils/adt/arrayfuncs.c
***************
*** 68,74 **** static void CopyArrayEls(ArrayType *array,
Datum *values, bool *nulls, int nitems,
int typlen, bool typbyval, char typalign,
bool freedata);
- static bool array_get_isnull(const bits8 *nullbitmap, int offset);
static void array_set_isnull(bits8 *nullbitmap, int offset, bool isNull);
static Datum ArrayCast(char *value, bool byval, int len);
static int ArrayCastAndSet(Datum src,
--- 68,73 ----
***************
*** 3829,3834 **** arraycontained(PG_FUNCTION_ARGS)
--- 3828,3996 ----
PG_RETURN_BOOL(result);
}
+ /*-----------------------------------------------------------------------------
+ * array iteration functions
+ * These functions are used to efficiently iterate through arrays
+ *-----------------------------------------------------------------------------
+ */
+
+ /*
+ * Create an iterator for the array 'arr' which will allow us to
+ * step through the array one slice at a time (where a slice
+ * is the size of the dimension indicated by slice_ndim).
+ */
+ ArrayReader
+ array_create_iterator(ArrayType *arr, int slice_ndim)
+ {
+ ArrayReader reader = palloc(sizeof(ArrayReaderData));
+
+ /*
+ * Store a pointer to the array itself, since we need
+ * various information from it as we build up arrays
+ * based off of it to return. No need to have the
+ * caller pass it in every time.
+ */
+ reader->arr = arr;
+
+ /*
+ * Initialize our data pointer to the beginning of the data
+ * segment, this pointer gets moved along as we move through
+ * the array.
+ */
+ reader->data_ptr = ARR_DATA_PTR(arr);
+
+ /*
+ * Similairly, initialize our current_item, this is used to
+ * check against the null bitmap for each element as we walk
+ * through the array.
+ */
+ reader->current_item = 0;
+
+ /*
+ * Get a pointer into the ARR_DIMS() array which starts where
+ * a given slice would start at, so that we have the dimensions
+ * of the slice itself when we're building an array to return.
+ */
+ reader->slice_dims_offset = ARR_DIMS(arr) + ARR_NDIM(arr) - slice_ndim;
+
+ /*
+ * Similairly, get a pointer into the ARR_LBOUND() which starts
+ * where a given slice would start at.
+ */
+ reader->slice_lbound_offset = ARR_LBOUND(arr) == 0 ? NULL :
+ ARR_LBOUND(arr) + ARR_NDIM(arr) - slice_ndim;
+
+ /*
+ * Gather this information during the set-up phase, since
+ * it involves a cache lookup and there's no sense doing
+ * that over and over.
+ */
+ get_typlenbyvalalign(ARR_ELEMTYPE(arr),
+ &reader->typlen,
+ &reader->typbyval,
+ &reader->typalign);
+
+ /* Get the total number of elements in the array */
+ reader->nitems = ArrayGetNItems(ARR_NDIM(arr), ARR_DIMS(arr));
+
+ reader->slice_len = ArrayGetNItems(slice_ndim, reader->slice_dims_offset);
+ reader->slice_ndim = slice_ndim;
+
+ return reader;
+ }
+
+ /*
+ * Iterate one slice at a time through the array which was passed to
+ * array_create_iterator based on the slice size requested, returning
+ * each slice via *value / *isnull
+ */
+ bool
+ array_iterate(ArrayReader reader, Datum *value, bool *isnull)
+ {
+ /* Check if we have reached the end of the array */
+ if (reader->current_item >= reader->nitems)
+ return false;
+
+ /*
+ * When slice_len is zero, we are just stepping through the array
+ * one element at a time. This is the nice, simple, case.
+ */
+ if (reader->slice_len == 0)
+ {
+ /* Return just one element. */
+ if (array_get_isnull(ARR_NULLBITMAP(reader->arr), reader->current_item++))
+ {
+ *isnull = true;
+ *value = (Datum) 0;
+ }
+ else
+ {
+ /* non-NULL, so fetch the individual Datum to return */
+ *isnull = false;
+ *value = fetch_att(reader->data_ptr, reader->typbyval,
+ reader->typlen);
+
+ /* Move our data pointer forward to the next element */
+ reader->data_ptr = att_addlength_pointer(reader->data_ptr,
+ reader->typlen,
+ reader->data_ptr);
+ reader->data_ptr = (char *) att_align_nominal(reader->data_ptr,
+ reader->typalign);
+ }
+ }
+ else
+ {
+ /*
+ * slice_len is non-zero, so we have to build and return an array which
+ * matches the slice size requested (which is the size of one of the
+ * dimensions of the array we're working on).
+ */
+
+ int i;
+ ArrayBuildState *astate = NULL;
+
+ /* Returns a slice. Copy "slice_len" elements to new array. */
+ for (i = 0; i < reader->slice_len; i++)
+ {
+ if (array_get_isnull(ARR_NULLBITMAP(reader->arr), reader->current_item++))
+ {
+ /* Add in a NULL element, if we come across one */
+ astate = accumArrayResult(astate, (Datum) 0, true,
+ ARR_ELEMTYPE(reader->arr),
+ CurrentMemoryContext);
+ }
+ else
+ {
+ Datum element;
+
+ /* Fetch each element and add it into the new array */
+ element = fetch_att(reader->data_ptr, reader->typbyval,
+ reader->typlen);
+
+ astate = accumArrayResult(astate, element, false,
+ ARR_ELEMTYPE(reader->arr),
+ CurrentMemoryContext);
+
+ /* Move our data pointer forward to the next element */
+ reader->data_ptr = att_addlength_pointer(reader->data_ptr,
+ reader->typlen,
+ reader->data_ptr);
+ reader->data_ptr = (char *) att_align_nominal(reader->data_ptr,
+ reader->typalign);
+ }
+ }
+
+ /* Build the final array to return */
+ *isnull = false;
+ *value = makeMdArrayResult(astate, reader->slice_ndim,
+ reader->slice_dims_offset,
+ reader->slice_lbound_offset,
+ CurrentMemoryContext, true);
+ }
+
+ return true;
+ }
+
/***************************************************************************/
/******************| Support Routines |*****************/
***************
*** 3840,3846 **** arraycontained(PG_FUNCTION_ARGS)
* nullbitmap: pointer to array's null bitmap (NULL if none)
* offset: 0-based linear element number of array element
*/
! static bool
array_get_isnull(const bits8 *nullbitmap, int offset)
{
if (nullbitmap == NULL)
--- 4002,4008 ----
* nullbitmap: pointer to array's null bitmap (NULL if none)
* offset: 0-based linear element number of array element
*/
! bool
array_get_isnull(const bits8 *nullbitmap, int offset)
{
if (nullbitmap == NULL)
*** a/src/include/utils/array.h
--- b/src/include/utils/array.h
***************
*** 115,120 **** typedef struct ArrayMapState
--- 115,148 ----
} ArrayMapState;
/*
+ * array_create_iterator() will return the below structure which is later
+ * used and updated by array_iterate() to allow the caller to efficiently
+ * step through an array one slice at a time. A slice is defined as one
+ * dimension of the base array.
+ */
+ typedef struct
+ {
+ /* 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 */
+
+ /* Cache'd info about the array that is pulled during initialization */
+ ArrayType *arr; /* array we're iterating through */
+ int nitems; /* total number of elements in array */
+ int16 typlen; /* element's length */
+ bool typbyval; /* element's byval property */
+ char typalign; /* align type of element */
+
+ /* information about the requested slice */
+ int slice_len; /* number of elements per slice */
+ int slice_ndim; /* slice dimension */
+ int *slice_dims_offset; /* slice dims array */
+ int *slice_lbound_offset; /* slice lbound array */
+ } ArrayReaderData;
+
+ typedef ArrayReaderData *ArrayReader;
+
+ /*
* fmgr macros for array objects
*/
#define DatumGetArrayTypeP(X) ((ArrayType *) PG_DETOAST_DATUM(X))
***************
*** 215,220 **** extern Datum array_ref(ArrayType *array, int nSubscripts, int *indx,
--- 243,249 ----
extern ArrayType *array_set(ArrayType *array, int nSubscripts, int *indx,
Datum dataValue, bool isNull,
int arraytyplen, int elmlen, bool elmbyval, char elmalign);
+ extern bool array_get_isnull(const bits8 *nullbitmap, int offset);
extern ArrayType *array_get_slice(ArrayType *array, int nSubscripts,
int *upperIndx, int *lowerIndx,
int arraytyplen, int elmlen, bool elmbyval, char elmalign);
***************
*** 254,259 **** extern Datum makeArrayResult(ArrayBuildState *astate,
--- 283,291 ----
extern Datum makeMdArrayResult(ArrayBuildState *astate, int ndims,
int *dims, int *lbs, MemoryContext rcontext, bool release);
+ extern ArrayReader array_create_iterator(ArrayType *arr, int slice_ndim);
+ extern bool array_iterate(ArrayReader reader, Datum *value, bool *isnull);
+
/*
* prototypes for functions defined in arrayutils.c
*/
*** a/src/pl/plpgsql/src/gram.y
--- b/src/pl/plpgsql/src/gram.y
***************
*** 21,26 ****
--- 21,27 ----
#include "parser/parse_type.h"
#include "parser/scanner.h"
#include "parser/scansup.h"
+ #include "utils/array.h"
/* Location tracking support --- simpler than bison's default */
***************
*** 134,139 **** static List *read_raise_options(void);
--- 135,141 ----
PLpgSQL_datum *scalar;
PLpgSQL_rec *rec;
PLpgSQL_row *row;
+ int slice;
} forvariable;
struct
{
***************
*** 178,184 **** static List *read_raise_options(void);
%type <ival> assign_var
%type <var> cursor_variable
%type <datum> decl_cursor_arg
! %type <forvariable> for_variable
%type <stmt> for_control
%type <str> any_identifier opt_block_label opt_label
--- 180,186 ----
%type <ival> assign_var
%type <var> cursor_variable
%type <datum> decl_cursor_arg
! %type <forvariable> for_variable foreach_control
%type <stmt> for_control
%type <str> any_identifier opt_block_label opt_label
***************
*** 190,196 **** 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 <list> proc_exceptions
%type <exception_block> exception_sect
--- 192,198 ----
%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 stmt_foreach_a
%type <list> proc_exceptions
%type <exception_block> exception_sect
***************
*** 264,269 **** static List *read_raise_options(void);
--- 266,272 ----
%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,303 **** static List *read_raise_options(void);
--- 301,307 ----
%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,744 **** proc_stmt : pl_block ';'
--- 743,750 ----
{ $$ = $1; }
| stmt_for
{ $$ = $1; }
+ | stmt_foreach_a
+ { $$ = $1; }
| stmt_exit
{ $$ = $1; }
| stmt_return
***************
*** 1386,1391 **** for_variable : T_DATUM
--- 1392,1455 ----
}
;
+ stmt_foreach_a : opt_block_label K_FOREACH foreach_control K_IN expr_until_loop loop_body
+ {
+ PLpgSQL_stmt_foreach_a *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->expr = $5;
+ new->slice = $3.slice;
+ new->body = $6.stmts;
+
+ if ($3.rec)
+ {
+ new->rec = $3.rec;
+ check_assignable((PLpgSQL_datum *) new->rec, @3);
+ }
+ else if ($3.row)
+ {
+ new->row = $3.row;
+ check_assignable((PLpgSQL_datum *) new->row, @3);
+ }
+ else if ($3.scalar)
+ {
+ Assert($3.scalar->dtype == PLPGSQL_DTYPE_VAR);
+ new->var = (PLpgSQL_var *) $3.scalar;
+ check_assignable($3.scalar, @3);
+
+ }
+ else
+ {
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("loop variable of loop over array must be a record, row variable, scalar variable or list of scalar variables"),
+ parser_errposition(@3)));
+ }
+
+ check_labels($1, $6.end_label, $6.end_label_location);
+ $$ = (PLpgSQL_stmt *) new;
+ }
+ ;
+
+ foreach_control : for_variable
+ {
+ $$ = $1;
+ $$.slice = 0;
+ }
+ | for_variable K_SLICE ICONST
+ {
+ $$ = $1;
+ $$.slice = $3;
+ if ($3 < 0 || $3 >= MAXDIM)
+ ereport(ERROR,
+ (errcode(ERRCODE_PROGRAM_LIMIT_EXCEEDED),
+ errmsg("number of slicing array dimensions (%d) exceeds the maximum allowed (%d)",
+ $3, MAXDIM),
+ parser_errposition(@3)));
+ }
+ ;
+
stmt_exit : exit_type opt_label opt_exitcond
{
PLpgSQL_stmt_exit *new;
***************
*** 2063,2068 **** unreserved_keyword :
--- 2127,2133 ----
| K_ROW_COUNT
| K_ROWTYPE
| K_SCROLL
+ | K_SLICE
| K_SQLSTATE
| K_TYPE
| K_USE_COLUMN
*** a/src/pl/plpgsql/src/pl_exec.c
--- b/src/pl/plpgsql/src/pl_exec.c
***************
*** 107,112 **** static int exec_stmt_fors(PLpgSQL_execstate *estate,
--- 107,114 ----
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,1317 **** exec_stmt(PLpgSQL_execstate *estate, PLpgSQL_stmt *stmt)
--- 1314,1323 ----
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;
***************
*** 2026,2031 **** exec_stmt_forc(PLpgSQL_execstate *estate, PLpgSQL_stmt_forc *stmt)
--- 2032,2231 ----
return rc;
}
+ /* ----------
+ * exec_stmt_foreach_a Implements loop over array
+ *
+ * This allows iterating over an array using either a single-step
+ * approach, or by doing it in slices where a slice is one dimension
+ * of the array. When single-step'ing, the loop variable is the same
+ * type that the array stores (eg: integer), when looping through
+ * slices, the loop variable is, itself, 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)
+ {
+ /* variable which will be changing as we loop over the array */
+ Datum value;
+ bool isnull;
+
+ ArrayType *arr; /* Our copy of the array */
+ Oid valtype; /* array value type */
+ Oid elmOid;
+
+ /* control variable */
+ PLpgSQL_datum *ctrl_var = NULL; /* make compiler quiet */
+ Oid ctrl_var_type = InvalidOid;
+
+ /* return variables */
+ bool found = false;
+ int rc = PLPGSQL_RC_OK;
+
+ /* Our iterator for looping over the array */
+ ArrayReader array_iterator;
+
+ /* get the value of the array expression using array_expr */
+ value = exec_eval_expr(estate, stmt->expr, &isnull, &valtype);
+ if (isnull)
+ ereport(ERROR,
+ (errcode(ERRCODE_NULL_VALUE_NOT_ALLOWED),
+ errmsg("NULL value isn't allowed as parameter of FOREACH-IN")));
+
+ /* check the type of the expression - must be an array */
+ if (!OidIsValid(get_element_type(valtype)))
+ ereport(ERROR,
+ (errcode(ERRCODE_DATATYPE_MISMATCH),
+ errmsg("result of expression isn't an array"),
+ errdetail("result of expression is %s",
+ format_type_be(valtype))));
+
+ /* make a copy of the array, is this necessary? */
+ arr = DatumGetArrayTypePCopy(value);
+
+ /* Clean up any leftover temporary memory */
+ exec_eval_cleanup(estate);
+
+ /*
+ * If the target needs to be an array, check that it actually is,
+ * and that it has a valid dimension for the array we're looping
+ * through.
+ */
+
+ /* Attempt to get the element OID, for error checking below */
+ elmOid = InvalidOid;
+ if (stmt->var != NULL)
+ elmOid = get_element_type(stmt->var->datatype->typoid);
+
+ /* slice dimension should be less or equal to array dimension */
+ if (stmt->slice > ARR_NDIM(arr))
+ ereport(ERROR,
+ (errcode(ERRCODE_NUMERIC_VALUE_OUT_OF_RANGE),
+ errmsg("slice dimension requested (%d) is higher than the number of array dimensions (%d)",
+ stmt->slice, ARR_NDIM(arr))));
+
+ /* Trying to do a slice, but we weren't given an array variable */
+ if (stmt->slice > 0 && elmOid == InvalidOid)
+ ereport(ERROR,
+ (errcode(ERRCODE_DATATYPE_MISMATCH),
+ errmsg("target variable \"%s\" for sliced array should be an array type",
+ stmt->var ? stmt->var->refname :
+ stmt->row ? stmt->row->refname :
+ stmt->rec ? stmt->rec->refname : "Unknown"),
+ errhint("Value assigned will be of an array type.")));
+
+ /* Trying to loop through as a scalar but given an array variable */
+ if (stmt->slice == 0 && stmt->var != NULL && elmOid != InvalidOid)
+ ereport(ERROR,
+ (errcode(ERRCODE_DATATYPE_MISMATCH),
+ errmsg("target variable \"%s\" is an array",
+ stmt->var->refname),
+ errhint("Value assigned will be of a scalar type")));
+
+ /* Set up the control variable/variable type */
+ if (stmt->slice > 0)
+ {
+ /* Doing array-based slices, set up our control variable */
+ ctrl_var_type = valtype;
+ ctrl_var = estate->datums[stmt->var->dno];
+ }
+ else
+ {
+ /* Looping one element at a time */
+
+ /* without slicing, loop var is the same type as an array element */
+ ctrl_var_type = ARR_ELEMTYPE(arr);
+
+ /* Figure out what kind of variable the element is going to be */
+ if (stmt->var != NULL)
+ ctrl_var = estate->datums[stmt->var->dno];
+ else if (stmt->row != NULL)
+ ctrl_var = estate->datums[stmt->row->dno];
+ else
+ ctrl_var = estate->datums[stmt->rec->dno];
+ }
+
+ /* Create an iterator to step through the array, a slice at a time */
+ array_iterator = array_create_iterator(arr, stmt->slice);
+
+ /*
+ * Iterate over the array, a slice at a time, and execute the statements
+ * in the block with the variable set to the current element or slice
+ */
+ while (array_iterate(array_iterator, &value, &isnull))
+ {
+ /* There is minimally one loop, so assign "found" to true */
+ found = true;
+
+ /* Fill in the control variable */
+ exec_assign_value(estate, ctrl_var, value, ctrl_var_type, &isnull);
+
+ /*
+ * 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;
+ }
+ }
+ }
+
+ pfree(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
*** a/src/pl/plpgsql/src/pl_funcs.c
--- b/src/pl/plpgsql/src/pl_funcs.c
***************
*** 230,235 **** plpgsql_stmt_typename(PLpgSQL_stmt *stmt)
--- 230,237 ----
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:
***************
*** 293,298 **** static void dump_cursor_direction(PLpgSQL_stmt_fetch *stmt);
--- 295,301 ----
static void dump_close(PLpgSQL_stmt_close *stmt);
static void dump_perform(PLpgSQL_stmt_perform *stmt);
static void dump_expr(PLpgSQL_expr *expr);
+ static void dump_foreach_a(PLpgSQL_stmt_foreach_a *stmt);
static void
***************
*** 376,381 **** dump_stmt(PLpgSQL_stmt *stmt)
--- 379,387 ----
case PLPGSQL_STMT_PERFORM:
dump_perform((PLpgSQL_stmt_perform *) stmt);
break;
+ case PLPGSQL_STMT_FOREACH_A:
+ dump_foreach_a((PLpgSQL_stmt_foreach_a *) stmt);
+ break;
default:
elog(ERROR, "unrecognized cmd_type: %d", stmt->cmd_type);
break;
***************
*** 596,601 **** dump_forc(PLpgSQL_stmt_forc *stmt)
--- 602,625 ----
}
static void
+ dump_foreach_a(PLpgSQL_stmt_foreach_a *stmt)
+ {
+ dump_ind();
+ printf("FOREACHA %s", (stmt->rec != NULL) ? stmt->rec->refname :
+ (stmt->row != NULL) ? stmt->row->refname : stmt->var->refname);
+ if (stmt->slice != 0)
+ printf("SLICE %d ", stmt->slice);
+ printf("IN ");
+ dump_expr(stmt->expr);
+ printf("\n");
+
+ dump_stmts(stmt->body);
+
+ dump_ind();
+ printf(" ENDOFOREACHA");
+ }
+
+ static void
dump_open(PLpgSQL_stmt_open *stmt)
{
dump_ind();
*** a/src/pl/plpgsql/src/pl_scanner.c
--- b/src/pl/plpgsql/src/pl_scanner.c
***************
*** 77,82 **** static const ScanKeyword reserved_keywords[] = {
--- 77,83 ----
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)
***************
*** 133,138 **** static const ScanKeyword unreserved_keywords[] = {
--- 134,140 ----
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)
*** a/src/pl/plpgsql/src/plpgsql.h
--- b/src/pl/plpgsql/src/plpgsql.h
***************
*** 87,92 **** enum PLpgSQL_stmt_types
--- 87,93 ----
PLPGSQL_STMT_CASE,
PLPGSQL_STMT_LOOP,
PLPGSQL_STMT_WHILE,
+ PLPGSQL_STMT_FOREACH_A,
PLPGSQL_STMT_FORI,
PLPGSQL_STMT_FORS,
PLPGSQL_STMT_FORC,
***************
*** 427,432 **** typedef struct
--- 428,447 ----
typedef struct
+ { /* FOREACH item in array loop */
+ int cmd_type;
+ int lineno;
+ char *label;
+ PLpgSQL_var *var;
+ PLpgSQL_rec *rec;
+ PLpgSQL_row *row;
+ PLpgSQL_expr *expr;
+ int slice;
+ List *body; /* List of statements */
+ } PLpgSQL_stmt_foreach_a;
+
+
+ typedef struct
{ /* FOR statement with integer loopvar */
int cmd_type;
int lineno;
*** a/src/test/regress/expected/plpgsql.out
--- b/src/test/regress/expected/plpgsql.out
***************
*** 4240,4242 **** select unreserved_test();
--- 4240,4436 ----
(1 row)
drop function unreserved_test();
+ -- Checking a FOREACH statement
+ create function foreach_test(anyarray)
+ returns void as $$
+ declare x int;
+ begin
+ foreach x in $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 $1
+ loop
+ raise notice '%', x;
+ end loop;
+ end;
+ $$ language plpgsql;
+ -- should fail
+ select foreach_test(ARRAY[1,2,3,4]);
+ ERROR: target variable "x" for sliced array should be an array type
+ HINT: Value assigned will 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: target variable "x" for sliced array should be an array type
+ HINT: Value assigned will 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 $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 $1
+ loop
+ raise notice '%', x;
+ end loop;
+ end;
+ $$ language plpgsql;
+ -- should fail
+ select foreach_test(ARRAY[1,2,3,4]);
+ ERROR: slice dimension requested (2) is higher than the number of array dimensions (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 $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 $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 $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;
*** a/src/test/regress/sql/plpgsql.sql
--- b/src/test/regress/sql/plpgsql.sql
***************
*** 3375,3377 **** $$ language plpgsql;
--- 3375,3488 ----
select unreserved_test();
drop function unreserved_test();
+
+ -- Checking a FOREACH statement
+ create function foreach_test(anyarray)
+ returns void as $$
+ declare x int;
+ begin
+ foreach x in $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 $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 $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 $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 $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 $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 $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;
signature.asc
Description: Digital signature
