for-in-array
application/octet-stream
Filename: for-in-array
Type: application/octet-stream
Part: 0
Message:
final patch - plpgsql: for-in-array
*** ./doc/src/sgml/plpgsql.sgml.orig 2010-09-29 10:40:40.000000000 +0200
--- ./doc/src/sgml/plpgsql.sgml 2010-09-30 14:37:45.005025323 +0200
***************
*** 2215,2220 ****
--- 2215,2259 ----
</para>
</sect2>
+ <sect2 id="plpgsql-array-iterating">
+ <title>Looping Through Array</title>
+
+ <para>
+ The syntax is:
+ <synopsis>
+ <optional> <<<replaceable>label</replaceable>>> </optional>
+ FOR <replaceable>target</replaceable> IN <replaceable>array expression</replaceable> LOOP
+ <replaceable>statements</replaceable>
+ END LOOP <optional> <replaceable>label</replaceable> </optional>;
+ </synopsis>
+
+ The <replaceable>target</replaceable> is a record variable, row variable,
+ or comma-separated list of scalar variables.
+ The <replaceable>target</replaceable> is successively assigned each item
+ of result of the <replaceable>array_expression</replaceable> and the loop body is
+ executed for each item. Here is an example:
+
+ <programlisting>
+ CREATE TYPE mypt AS (x int, y int);
+
+ CREATE FUNCTION iterate_over_points() RETURNS void AS $$
+ DECLARE
+ x int; y int;
+ a mypt[] = ARRAY[(10,11),(20,21),(30,31)];
+ BEGIN
+ FOR x, y IN ARRAY a
+ LOOP
+ RAISE NOTICE 'x = %, y = %', x, y;
+ END LOOP;
+ END;
+ $$ LANGUAGE plpgsql;
+ </programlisting>
+
+ If the loop is terminated by an <literal>EXIT</> statement, the last
+ assigned item value is still accessible after the loop.
+ </para>
+ </sect2>
+
<sect2 id="plpgsql-error-trapping">
<title>Trapping Errors</title>
*** ./src/pl/plpgsql/src/gram.y.orig 2010-09-29 10:53:44.663270537 +0200
--- ./src/pl/plpgsql/src/gram.y 2010-09-30 13:27:37.274024632 +0200
***************
*** 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
***************
*** 1051,1056 ****
--- 1052,1066 ----
new->body = $4.stmts;
$$ = (PLpgSQL_stmt *) new;
}
+ else if ($3->cmd_type == PLPGSQL_STMT_FORA)
+ {
+ PLpgSQL_stmt_fora *new;
+ new = (PLpgSQL_stmt_fora *) $3;
+ new->lineno = plpgsql_location_to_lineno(@2);
+ new->label = $1;
+ new->body = $4.stmts;
+ $$ = (PLpgSQL_stmt *) new;
+ }
else
{
PLpgSQL_stmt_forq *new;
***************
*** 1077,1083 ****
int tok = yylex();
int tokloc = yylloc;
! if (tok == K_EXECUTE)
{
/* EXECUTE means it's a dynamic FOR loop */
PLpgSQL_stmt_dynfors *new;
--- 1087,1127 ----
int tok = yylex();
int tokloc = yylloc;
! if (tok == K_ARRAY)
! {
! PLpgSQL_stmt_fora *new;
! PLpgSQL_expr *expr;
!
! new = palloc0(sizeof(PLpgSQL_stmt_fora));
! new->cmd_type = PLPGSQL_STMT_FORA;
!
! new->expr = read_sql_expression(K_LOOP, "LOOP");
!
! if ($1.rec)
! {
! new->ctrl_var = (PLpgSQL_variable *) $1.rec;
! }
! else if ($1.row)
! {
! new->ctrl_var = (PLpgSQL_variable *) $1.row;
! }
! else if ($1.scalar)
! {
! new->ctrl_var = (PLpgSQL_variable *) $1.scalar;
! }
! else
! {
! ereport(ERROR,
! (errcode(ERRCODE_DATATYPE_MISMATCH),
! errmsg("loop variable of loop over array must be a record or row variable or list of scalar variables"),
! parser_errposition(@1)));
! }
!
! check_assignable((PLpgSQL_datum *) new->ctrl_var, @1);
!
! $$ = (PLpgSQL_stmt *) new;
! }
! else if (tok == K_EXECUTE)
{
/* EXECUTE means it's a dynamic FOR loop */
PLpgSQL_stmt_dynfors *new;
*** ./src/pl/plpgsql/src/pl_exec.c.orig 2010-09-29 11:22:32.435395512 +0200
--- ./src/pl/plpgsql/src/pl_exec.c 2010-09-30 14:39:05.804899980 +0200
***************
*** 107,112 ****
--- 107,114 ----
PLpgSQL_stmt_fors *stmt);
static int exec_stmt_forc(PLpgSQL_execstate *estate,
PLpgSQL_stmt_forc *stmt);
+ static int exec_stmt_fora(PLpgSQL_execstate *estate,
+ PLpgSQL_stmt_fora *stmt);
static int exec_stmt_open(PLpgSQL_execstate *estate,
PLpgSQL_stmt_open *stmt);
static int exec_stmt_fetch(PLpgSQL_execstate *estate,
***************
*** 1309,1314 ****
--- 1311,1320 ----
rc = exec_stmt_forc(estate, (PLpgSQL_stmt_forc *) stmt);
break;
+ case PLPGSQL_STMT_FORA:
+ rc = exec_stmt_fora(estate, (PLpgSQL_stmt_fora *) stmt);
+ break;
+
case PLPGSQL_STMT_EXIT:
rc = exec_stmt_exit(estate, (PLpgSQL_stmt_exit *) stmt);
break;
***************
*** 1862,1899 ****
/* ----------
- * exec_stmt_fors Execute a query, assign each
- * tuple to a record or row and
- * execute a group of statements
- * for it.
- * ----------
- */
- static int
- exec_stmt_fors(PLpgSQL_execstate *estate, PLpgSQL_stmt_fors *stmt)
- {
- Portal portal;
- int rc;
-
- /*
- * Open the implicit cursor for the statement using exec_run_select
- */
- exec_run_select(estate, stmt->query, 0, &portal);
-
- /*
- * Execute the loop
- */
- rc = exec_for_query(estate, (PLpgSQL_stmt_forq *) stmt, portal, true);
-
- /*
- * Close the implicit cursor
- */
- SPI_cursor_close(portal);
-
- return rc;
- }
-
-
- /* ----------
* exec_stmt_forc Execute a loop for each row from a cursor.
* ----------
*/
--- 1868,1873 ----
***************
*** 2023,2028 ****
--- 1997,2182 ----
return rc;
}
+ /* ----------
+ * exec_stmt_fors Execute a query, assign each
+ * tuple to a record or row and
+ * execute a group of statements
+ * for it.
+ * ----------
+ */
+ static int
+ exec_stmt_fors(PLpgSQL_execstate *estate, PLpgSQL_stmt_fors *stmt)
+ {
+ Portal portal;
+ int rc;
+
+ /*
+ * Open the implicit cursor for the statement using exec_run_select
+ */
+ exec_run_select(estate, stmt->query, 0, &portal);
+
+ /*
+ * Execute the loop
+ */
+ rc = exec_for_query(estate, (PLpgSQL_stmt_forq *) stmt, portal, true);
+
+ /*
+ * Close the implicit cursor
+ */
+ SPI_cursor_close(portal);
+
+ return rc;
+ }
+
+ /* ----------
+ * exec_stmt_fora Execute a loop for each field of array
+ * ----------
+ */
+ static int
+ exec_stmt_fora(PLpgSQL_execstate *estate, PLpgSQL_stmt_fora *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;
+
+ /* 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 FOR-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))));
+
+ /* get a controll variable */
+ ctrl_var = estate->datums[stmt->ctrl_var->dno];
+
+ /* 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);
+
+ /*
+ * Now do the loop
+ */
+ for (idx = 0; idx < numelems; idx++)
+ {
+ found = true; /* looped at least once */
+
+ 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);
+ }
+
+ /*
+ * 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.
+ */
+ exec_assign_value(estate, ctrl_var,
+ value, array_typelem, &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
***************
*** 2064,2070 ****
exec_stmt_return(PLpgSQL_execstate *estate, PLpgSQL_stmt_return *stmt)
{
/*
! * If processing a set-returning PL/pgSQL function, the final RETURN
* indicates that the function is finished producing tuples. The rest of
* the work will be done at the top level.
*/
--- 2218,2224 ----
exec_stmt_return(PLpgSQL_execstate *estate, PLpgSQL_stmt_return *stmt)
{
/*
! * If processing a set-returning PL/PgSQL function, the final RETURN
* indicates that the function is finished producing tuples. The rest of
* the work will be done at the top level.
*/
*** ./src/pl/plpgsql/src/pl_funcs.c.orig 2010-09-29 11:29:27.956270569 +0200
--- ./src/pl/plpgsql/src/pl_funcs.c 2010-09-30 13:30:26.082899679 +0200
***************
*** 230,235 ****
--- 230,237 ----
return _("FOR over SELECT rows");
case PLPGSQL_STMT_FORC:
return _("FOR over cursor");
+ case PLPGSQL_STMT_FORA:
+ return _("FOR over array");
case PLPGSQL_STMT_EXIT:
return "EXIT";
case PLPGSQL_STMT_RETURN:
***************
*** 277,282 ****
--- 279,285 ----
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_fora(PLpgSQL_stmt_fora *stmt);
static void dump_forc(PLpgSQL_stmt_forc *stmt);
static void dump_exit(PLpgSQL_stmt_exit *stmt);
static void dump_return(PLpgSQL_stmt_return *stmt);
***************
*** 337,342 ****
--- 340,348 ----
case PLPGSQL_STMT_FORC:
dump_forc((PLpgSQL_stmt_forc *) stmt);
break;
+ case PLPGSQL_STMT_FORA:
+ dump_fora((PLpgSQL_stmt_fora *) stmt);
+ break;
case PLPGSQL_STMT_EXIT:
dump_exit((PLpgSQL_stmt_exit *) stmt);
break;
***************
*** 596,601 ****
--- 602,619 ----
}
static void
+ dump_fora(PLpgSQL_stmt_fora *stmt)
+ {
+ dump_ind();
+ printf("FORA %s ", stmt->ctrl_var->refname);
+ dump_expr(stmt->expr);
+ printf("\n");
+ dump_stmts(stmt->body);
+ dump_ind();
+ printf(" ENDFORA\n");
+ }
+
+ static void
dump_open(PLpgSQL_stmt_open *stmt)
{
dump_ind();
*** ./src/pl/plpgsql/src/plpgsql.h.orig 2010-09-29 11:10:35.745270541 +0200
--- ./src/pl/plpgsql/src/plpgsql.h 2010-09-30 13:27:03.985025017 +0200
***************
*** 87,92 ****
--- 87,93 ----
PLPGSQL_STMT_CASE,
PLPGSQL_STMT_LOOP,
PLPGSQL_STMT_WHILE,
+ PLPGSQL_STMT_FORA,
PLPGSQL_STMT_FORI,
PLPGSQL_STMT_FORS,
PLPGSQL_STMT_FORC,
***************
*** 440,447 ****
/*
* PLpgSQL_stmt_forq represents a FOR statement running over a SQL query.
! * It is the common supertype of PLpgSQL_stmt_fors, PLpgSQL_stmt_forc
! * and PLpgSQL_dynfors.
*/
typedef struct
{
--- 441,448 ----
/*
* PLpgSQL_stmt_forq represents a FOR statement running over a SQL query.
! * It is the common supertype of PLpgSQL_stmt_fors, PLpgSQL_stmt_forc,
! * PLpgSQL_stmt_fora and PLpgSQL_dynfors.
*/
typedef struct
{
***************
*** 454,459 ****
--- 455,470 ----
} PLpgSQL_stmt_forq;
typedef struct
+ {
+ int cmd_type;
+ int lineno;
+ char *label;
+ PLpgSQL_variable *ctrl_var;
+ List *body; /* List of statements */
+ PLpgSQL_expr *expr;
+ } PLpgSQL_stmt_fora;
+
+ typedef struct
{ /* FOR statement running over SELECT */
int cmd_type;
int lineno;
*** ./src/pl/plpgsql/src/pl_scanner.c.orig 2010-09-30 08:47:40.631901895 +0200
--- ./src/pl/plpgsql/src/pl_scanner.c 2010-09-29 11:02:01.005270250 +0200
***************
*** 60,65 ****
--- 60,66 ----
static const ScanKeyword reserved_keywords[] = {
PG_KEYWORD("all", K_ALL, RESERVED_KEYWORD)
+ PG_KEYWORD("array", K_ARRAY, RESERVED_KEYWORD)
PG_KEYWORD("begin", K_BEGIN, RESERVED_KEYWORD)
PG_KEYWORD("by", K_BY, RESERVED_KEYWORD)
PG_KEYWORD("case", K_CASE, RESERVED_KEYWORD)
*** ./src/test/regress/expected/plpgsql.out.orig 2010-09-29 10:40:40.000000000 +0200
--- ./src/test/regress/expected/plpgsql.out 2010-09-30 14:03:42.000000000 +0200
***************
*** 4193,4195 ****
--- 4193,4342 ----
(1 row)
drop function unreserved_test();
+ create type fora_point as (x int, y int);
+ -- test for-in-array
+ create or replace function fora_test()
+ returns int as $$
+ declare x int; s int = 0;
+ begin
+ for x in array array[1,2,3,4,5,6,7,8,9,10]
+ loop
+ s := s + x;
+ end loop;
+ return s;
+ end;
+ $$ language plpgsql;
+ select fora_test();
+ fora_test
+ -----------
+ 55
+ (1 row)
+
+ create or replace function subscripts(anyarray, int)
+ returns int[] as $$
+ select array(select generate_subscripts($1,$2));
+ $$ language sql;
+ create or replace function fora_test()
+ returns int as $$
+ declare x int; s int = 0;
+ a int[] := array[1,2,3,4,5,6,7,8,9,10];
+ begin
+ for x in array subscripts(a, 1)
+ loop
+ s := s + a[x];
+ end loop;
+ return s;
+ end;
+ $$ language plpgsql;
+ select fora_test();
+ fora_test
+ -----------
+ 55
+ (1 row)
+
+ create or replace function fora_test()
+ returns int as $$
+ declare x numeric; s numeric = 0;
+ a numeric[] := array[1,2,3,4,5,6,7,8,9,10];
+ begin
+ for x in array subscripts(a, 1)
+ loop
+ s := s + a[x];
+ end loop;
+ return s::int;
+ end;
+ $$ language plpgsql;
+ select fora_test();
+ fora_test
+ -----------
+ 55
+ (1 row)
+
+ drop function subscripts(anyarray, int);
+ create or replace function fora_test()
+ returns int as $$
+ declare x text;
+ a text[] := array['Lenka','Jana','Magda','Lida'];
+ begin
+ for x in array a
+ loop
+ raise notice 'name=%', x;
+ end loop;
+ return 0;
+ end;
+ $$ language plpgsql;
+ select fora_test();
+ NOTICE: name=Lenka
+ NOTICE: name=Jana
+ NOTICE: name=Magda
+ NOTICE: name=Lida
+ fora_test
+ -----------
+ 0
+ (1 row)
+
+ create or replace function fora_test()
+ returns int as $$
+ declare x fora_point;
+ a fora_point[] := array[(1,2),(3,4),(5,6)];
+ begin
+ for x in array a
+ loop
+ raise notice 'point=%', x;
+ end loop;
+ return 0;
+ end;
+ $$ language plpgsql;
+ select fora_test();
+ NOTICE: point=(1,2)
+ NOTICE: point=(3,4)
+ NOTICE: point=(5,6)
+ fora_test
+ -----------
+ 0
+ (1 row)
+
+ create or replace function fora_test()
+ returns int as $$
+ declare x record;
+ a fora_point[] := array[(1,2),(3,4),(5,6)];
+ begin
+ for x in array a
+ loop
+ raise notice 'point=%', x.x;
+ end loop;
+ return 0;
+ end;
+ $$ language plpgsql;
+ select fora_test();
+ NOTICE: point=1
+ NOTICE: point=3
+ NOTICE: point=5
+ fora_test
+ -----------
+ 0
+ (1 row)
+
+ create or replace function fora_test()
+ returns int as $$
+ declare x int; y int;
+ a fora_point[] := array[(1,2),(3,4),(5,6)];
+ begin
+ for x, y in array a
+ loop
+ raise notice 'point=%,%', x, y;
+ end loop;
+ return 0;
+ end;
+ $$ language plpgsql;
+ select fora_test();
+ NOTICE: point=1,2
+ NOTICE: point=3,4
+ NOTICE: point=5,6
+ fora_test
+ -----------
+ 0
+ (1 row)
+
+ drop function fora_test();
+ drop type fora_point;
*** ./src/test/regress/sql/plpgsql.sql.orig 2010-09-29 10:40:40.000000000 +0200
--- ./src/test/regress/sql/plpgsql.sql 2010-09-30 14:03:17.478899984 +0200
***************
*** 3333,3335 ****
--- 3333,3452 ----
select unreserved_test();
drop function unreserved_test();
+
+ create type fora_point as (x int, y int);
+
+ -- test for-in-array
+ create or replace function fora_test()
+ returns int as $$
+ declare x int; s int = 0;
+ begin
+ for x in array array[1,2,3,4,5,6,7,8,9,10]
+ loop
+ s := s + x;
+ end loop;
+ return s;
+ end;
+ $$ language plpgsql;
+
+ select fora_test();
+
+ create or replace function subscripts(anyarray, int)
+ returns int[] as $$
+ select array(select generate_subscripts($1,$2));
+ $$ language sql;
+
+ create or replace function fora_test()
+ returns int as $$
+ declare x int; s int = 0;
+ a int[] := array[1,2,3,4,5,6,7,8,9,10];
+ begin
+ for x in array subscripts(a, 1)
+ loop
+ s := s + a[x];
+ end loop;
+ return s;
+ end;
+ $$ language plpgsql;
+
+ select fora_test();
+
+ create or replace function fora_test()
+ returns int as $$
+ declare x numeric; s numeric = 0;
+ a numeric[] := array[1,2,3,4,5,6,7,8,9,10];
+ begin
+ for x in array subscripts(a, 1)
+ loop
+ s := s + a[x];
+ end loop;
+ return s::int;
+ end;
+ $$ language plpgsql;
+
+ select fora_test();
+
+ drop function subscripts(anyarray, int);
+
+ create or replace function fora_test()
+ returns int as $$
+ declare x text;
+ a text[] := array['Lenka','Jana','Magda','Lida'];
+ begin
+ for x in array a
+ loop
+ raise notice 'name=%', x;
+ end loop;
+ return 0;
+ end;
+ $$ language plpgsql;
+
+ select fora_test();
+
+ create or replace function fora_test()
+ returns int as $$
+ declare x fora_point;
+ a fora_point[] := array[(1,2),(3,4),(5,6)];
+ begin
+ for x in array a
+ loop
+ raise notice 'point=%', x;
+ end loop;
+ return 0;
+ end;
+ $$ language plpgsql;
+
+ select fora_test();
+
+ create or replace function fora_test()
+ returns int as $$
+ declare x record;
+ a fora_point[] := array[(1,2),(3,4),(5,6)];
+ begin
+ for x in array a
+ loop
+ raise notice 'point=%', x.x;
+ end loop;
+ return 0;
+ end;
+ $$ language plpgsql;
+
+ select fora_test();
+
+ create or replace function fora_test()
+ returns int as $$
+ declare x int; y int;
+ a fora_point[] := array[(1,2),(3,4),(5,6)];
+ begin
+ for x, y in array a
+ loop
+ raise notice 'point=%,%', x, y;
+ end loop;
+ return 0;
+ end;
+ $$ language plpgsql;
+
+ select fora_test();
+
+ drop function fora_test();
+ drop type fora_point;