Hello
I am resending a redesigned proposal about special plpgsql statement
that support iteration over an array.
The most conflict issue of last proposal was a syntax. It enhanced
relative complex FOR statement. So now, it's based on new statement
with simple syntax. We can use a keyword FOREACH, this isn't in
conflict with PL/SQL - use a keyword FORALL and it isn't in conflict
with SQL/PSM too. More - this special statement can be used for
PostgreSQL's specific purposes. It can carry a new features in future.
The design of proposed functionality is simple, but respects a
possibility for enhancing a FOREACH cycle for future.
==proposed syntax:==
[ <<label>> ]
FOREACH var [, var [..]] IN ARRAY expr
LOOP
...
END LOOP [ label ]
==the goals:==
* cleaner syntax for full iteration over array
* reduce a overhead from only seq. access to any field in array
(it's not too significant)
* simplify iteration over multidimensional arrays
The most performance issue of access to a untoasted array is "solved"
with other patch.
== Iteration over multidimensional arrays ==
Its designed to reduce one dimension from source array. It can remove
a slicing and simplify code:
CREATE OR REPLACE FUNCTION public.fa(anyarray)
RETURNS void
LANGUAGE plpgsql
AS $function$
DECLARE i int[];
BEGIN
FOREACH i IN ARRAY $1
LOOP
RAISE NOTICE '%', i;
END LOOP;
END;
$function$
postgres=# select fa(array[[[1,2],[3,4]],[[1,2],[3,4]],[[5,6],[7,8]]]);
NOTICE: {{1,2},{3,4}}
NOTICE: {{1,2},{3,4}}
NOTICE: {{5,6},{7,8}}
fa
----
(1 row)
postgres=# select fa(array[[1,2,3,4],[1,2,3,4],[5,6,7,8]]);
NOTICE: {1,2,3,4}
NOTICE: {1,2,3,4}
NOTICE: {5,6,7,8}
fa
----
(1 row)
ideas, notes?
Regards
Pavel
*** ./plpgsql/src/gram.y.orig 2010-12-16 09:10:44.464254014 +0100
--- ./plpgsql/src/gram.y 2010-12-16 10:24:45.620740197 +0100
***************
*** 190,196 ****
%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
--- 190,196 ----
%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
***************
*** 239,244 ****
--- 239,245 ----
%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,269 ****
--- 265,271 ----
%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
***************
*** 739,744 ****
--- 741,748 ----
{ $$ = $1; }
| stmt_for
{ $$ = $1; }
+ | stmt_foreach_a
+ { $$ = $1; }
| stmt_exit
{ $$ = $1; }
| stmt_return
***************
*** 1386,1391 ****
--- 1390,1434 ----
}
;
+ stmt_foreach_a : opt_block_label K_FOREACH for_variable K_IN K_ARRAY 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 = $6;
+ new->body = $7.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 arrat must be a record, row variable, scalar variable or list of scalar variables"),
+ parser_errposition(@3)));
+ }
+
+ check_labels($1, $7.end_label, $7.end_label_location);
+ $$ = (PLpgSQL_stmt *) new;
+ }
+ ;
+
stmt_exit : exit_type opt_label opt_exitcond
{
PLpgSQL_stmt_exit *new;
***************
*** 2035,2040 ****
--- 2078,2084 ----
unreserved_keyword :
K_ABSOLUTE
| K_ALIAS
+ | K_ARRAY
| K_BACKWARD
| K_CONSTANT
| K_CURSOR
*** ./plpgsql/src/pl_exec.c.orig 2010-12-16 10:25:37.285549156 +0100
--- ./plpgsql/src/pl_exec.c 2010-12-16 13:22:34.123447850 +0100
***************
*** 107,112 ****
--- 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 ****
--- 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;
***************
*** 2028,2033 ****
--- 2034,2240 ----
/* ----------
+ * exec_stmt_foreach_a Implements loop over array
+ *
+ * ----------
+ */
+ static int
+ exec_stmt_foreach_a(PLpgSQL_execstate *estate, PLpgSQL_stmt_foreach_a *stmt)
+ {
+ Datum value;
+ bool isnull;
+ Oid valtype;
+ int numelems = 0;
+ Oid array_typelem;
+ int idx;
+ ArrayType *arr;
+ char *ptr;
+ bits8 *arraynullsptr;
+ int16 elmlen;
+ bool elmbyval;
+ char elmalign;
+ PLpgSQL_datum *ctrl_var;
+ bool found = false;
+ int rc = PLPGSQL_RC_OK;
+ int nitems = 1;
+
+ /* get a result of 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-ARRAY")));
+
+ /* check a result of expression - must be a array */
+ array_typelem = get_element_type(valtype);
+
+ if (!OidIsValid(array_typelem))
+ ereport(ERROR,
+ (errcode(ERRCODE_DATATYPE_MISMATCH),
+ errmsg("result of expression isn't array"),
+ errdetail("result of expression is %s",
+ format_type_be(valtype))));
+
+ /* copy a result and takes some infos */
+ arr = DatumGetArrayTypePCopy(value);
+ numelems = ArrayGetNItems(ARR_NDIM(arr), ARR_DIMS(arr));
+ ptr = ARR_DATA_PTR(arr);
+ arraynullsptr = ARR_NULLBITMAP(arr);
+ get_typlenbyvalalign(ARR_ELEMTYPE(arr),
+ &elmlen,
+ &elmbyval,
+ &elmalign);
+
+ /* clean a stack */
+ exec_eval_cleanup(estate);
+
+ /* get a target variable */
+ if (stmt->var != NULL)
+ {
+ if (ARR_NDIM(arr) > 1)
+ {
+ nitems = ArrayGetNItems(ARR_NDIM(arr) - 1, ARR_DIMS(arr) + 1);;
+ }
+
+ ctrl_var = estate->datums[stmt->var->dno];
+ }
+ else if (stmt->row != NULL)
+ {
+ if (ARR_NDIM(arr) > 1)
+ elog(ERROR, "array must not be assigned to row variable");
+
+ ctrl_var = estate->datums[stmt->row->dno];
+ }
+ else
+ {
+ if (ARR_NDIM(arr) > 1)
+ elog(ERROR, "array must not be assigned to record variable");
+
+ ctrl_var = estate->datums[stmt->rec->dno];
+ }
+
+ /*
+ * Now do the loop
+ */
+ idx = 0;
+ while (idx < numelems)
+ {
+ int j;
+ ArrayBuildState *astate = NULL;
+
+ found = true; /* looped at least once */
+
+ for (j = 0; j < nitems; j++)
+ {
+ if (arraynullsptr != NULL && !(arraynullsptr[idx / 8] & (1 << (idx % 8))))
+ {
+ isnull = true;
+ value = (Datum) 0;
+ }
+ else
+ {
+ isnull = false;
+ value = fetch_att(ptr, elmbyval, elmlen);
+
+ ptr = att_addlength_pointer(ptr, elmlen, ptr);
+ ptr = (char *) att_align_nominal(ptr, elmalign);
+ }
+
+ if (nitems > 1)
+ {
+ astate = accumArrayResult(astate, value, isnull,
+ array_typelem,
+ CurrentMemoryContext);
+ }
+ idx++;
+ }
+
+ /*
+ * store a item to variable - we expecting so almost all
+ * iteration will be over scalar values, so it is reason
+ * why we don't create a fake tuple over scalar and we
+ * don't use a exec_move_row for scalars. This is about
+ * four times faster.
+ */
+ if (astate == NULL)
+ exec_assign_value(estate, ctrl_var,
+ value, array_typelem, &isnull);
+ else
+ {
+ Datum x;
+ bool isnull = false;
+
+ x = makeMdArrayResult(astate, ARR_NDIM(arr) - 1, ARR_DIMS(arr) + 1, ARR_LBOUND(arr) ? ARR_LBOUND(arr) + 1 : NULL,
+ CurrentMemoryContext, true);
+ exec_assign_value(estate, ctrl_var, x, valtype, &isnull);
+ }
+
+ /*
+ * Execute the statements
+ */
+ rc = exec_stmts(estate, stmt->body);
+
+ 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(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.
*** ./plpgsql/src/pl_funcs.c.orig 2010-12-16 10:06:08.805286073 +0100
--- ./plpgsql/src/pl_funcs.c 2010-12-16 10:09:28.134644301 +0100
***************
*** 230,235 ****
--- 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 ****
--- 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
dump_ind(void)
***************
*** 375,380 ****
--- 378,386 ----
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;
***************
*** 595,600 ****
--- 601,621 ----
}
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);
+ printf("IN ARRAY ");
+ dump_expr(stmt->expr);
+ printf("\n");
+
+ dump_stmts(stmt->body);
+
+ dump_ind();
+ printf(" ENDOFOREACHA");
+ }
+
+ static void
dump_open(PLpgSQL_stmt_open *stmt)
{
dump_ind();
*** ./plpgsql/src/plpgsql.h.orig 2010-12-16 09:14:42.645613534 +0100
--- ./plpgsql/src/plpgsql.h 2010-12-16 10:10:09.797151135 +0100
***************
*** 87,92 ****
--- 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 ****
--- 428,446 ----
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;
+ List *body; /* List of statements */
+ } PLpgSQL_stmt_foreach_a;
+
+
+ typedef struct
{ /* FOR statement with integer loopvar */
int cmd_type;
int lineno;
*** ./plpgsql/src/pl_scanner.c.orig 2010-12-16 09:11:11.797853910 +0100
--- ./plpgsql/src/pl_scanner.c 2010-12-16 09:51:13.255962643 +0100
***************
*** 77,82 ****
--- 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)
***************
*** 105,110 ****
--- 106,112 ----
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)
--
Sent via pgsql-hackers mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
