*** ./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 ---- + + Looping Through Array + + + The syntax is: + + <<label>> + FOR target IN array expression LOOP + statements + END LOOP label ; + + + The target is a record variable, row variable, + or comma-separated list of scalar variables. + The target is successively assigned each item + of result of the array_expression and the loop body is + executed for each item. Here is an example: + + + 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; + + + If the loop is terminated by an EXIT statement, the last + assigned item value is still accessible after the loop. + + + Trapping Errors *** ./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 K_ABSOLUTE %token K_ALIAS %token K_ALL + %token K_ARRAY %token K_BACKWARD %token K_BEGIN %token 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;