Hi small update
Now assignment to plpgsql array variable and json array works. Some minor code cleaning + more regress tests. Regards Pavel
From b0556c08fe031027ebd10dff8f9d4df5f18ac286 Mon Sep 17 00:00:00 2001 From: "[email protected]" <[email protected]> Date: Mon, 23 Feb 2026 12:53:44 +0100 Subject: [PATCH] FOREACH scalar IN JSON ARRAY this patch introduce FOREACH scalar_var IN JSON ARRAY. The design is based on behave of jsonb_array_elements functions. In this case, FOREACH enforce casting to target type (because we know target type) and try to reduce IO casting. Attention: IO casting can be more strict, then casting based on cast functions. DECLARE t int; BEGIN -- this can work because we use cast numeric -> int FOREACH t IN JSON ARRAY '[1,2,3.14]' LOOP -- this fails, because IO cast is used, and integer input function -- allows only digits FOREAC t IN JSON ARRAY '[1,2,3,"3.14"]' LOOP Conceptual question is if casting should be strict like "old" PostgreSQL json function or lax as "new" SQL/JSON functions? I can imagine lax mode as default with possibility to switch to strict mode (this is not implemented now): FOREACH t IN JSON ARRAY '[1,2,3]' ERROR ON EMPTY ERROR ON ERROR LOOP ... The performance (best case for iteration over 1000 fields array) is about 4x better than when FOR IN SELECT jsonb_array_elements is used. --- doc/src/sgml/plpgsql.sgml | 59 ++++ src/pl/plpgsql/src/Makefile | 2 +- .../plpgsql/src/expected/plpgsql_foreach.out | 192 ++++++++++++ src/pl/plpgsql/src/meson.build | 1 + src/pl/plpgsql/src/pl_exec.c | 287 ++++++++++++++++++ src/pl/plpgsql/src/pl_funcs.c | 29 ++ src/pl/plpgsql/src/pl_gram.y | 39 ++- src/pl/plpgsql/src/pl_unreserved_kwlist.h | 1 + src/pl/plpgsql/src/plpgsql.h | 36 ++- src/pl/plpgsql/src/sql/plpgsql_foreach.sql | 159 ++++++++++ 10 files changed, 797 insertions(+), 8 deletions(-) create mode 100644 src/pl/plpgsql/src/expected/plpgsql_foreach.out create mode 100644 src/pl/plpgsql/src/sql/plpgsql_foreach.sql diff --git a/doc/src/sgml/plpgsql.sgml b/doc/src/sgml/plpgsql.sgml index 561f6e50d63..034fbd8bd45 100644 --- a/doc/src/sgml/plpgsql.sgml +++ b/doc/src/sgml/plpgsql.sgml @@ -2780,6 +2780,65 @@ NOTICE: row = {10,11,12} </para> </sect2> + <sect2 id="plpgsql-foreach-json-array"> + <title>Looping through JSON arrays</title> + + <para> + The <literal>FOREACH</literal> loop is much like a <literal>FOREACH</literal> loop, + but instead of iterating through elements of the array, + it iterates through the elements of an JSON array value + (expression is internaly casted to jsonb type). + +<synopsis> +<optional> <<<replaceable>label</replaceable>>> </optional> +FOREACH <replaceable>target</replaceable> IN JSON ARRAY <replaceable>expression</replaceable> LOOP + <replaceable>statements</replaceable> +END LOOP <optional> <replaceable>label</replaceable> </optional>; +</synopsis> + </para> + + <para> + Target can be scalar variable, composite variable or list of + scalar variables. When variable is not scalar, then assigned value + should be a JSON object and the JSON attributes are assigned by names. + +<programlisting> +CREATE FUNCTION scan_rows(jsonb) RETURNS void AS $$ +DECLARE + x int; +BEGIN + FOREACH x IN JSON ARRAY $1 + LOOP + RAISE NOTICE 'row = %', x; + END LOOP; +END; +$$ LANGUAGE plpgsql; + +SELECT scan_rows('[1,2,3]'); +NOTICE: row = 1 +NOTICE: row = 2 +NOTICE: row = 3 + +CREATE FUNCTION scan_rows(jsonb) RETURNS void AS $$ +DECLARE + x int; y varchar; +BEGIN + FOREACH x, y IN JSON ARRAY $1 + LOOP + RAISE NOTICE 'x: %, y: %', x, y; + END LOOP; +END; +$$ LANGUAGE plpgsql; + +SELECT scan_rows('[{},{"x":10},{"y":"Hi"},{"y":"Hi", "x":1000}]'); +NOTICE: x: <NULL>, y: <NULL> +NOTICE: x: 10, y: <NULL> +NOTICE: x: <NULL>, y: Hi +NOTICE: x: 1000, y: Hi +</programlisting> + </para> + </sect2> + <sect2 id="plpgsql-error-trapping"> <title>Trapping Errors</title> diff --git a/src/pl/plpgsql/src/Makefile b/src/pl/plpgsql/src/Makefile index 63cb96fae3e..5bd0cf31dfc 100644 --- a/src/pl/plpgsql/src/Makefile +++ b/src/pl/plpgsql/src/Makefile @@ -35,7 +35,7 @@ REGRESS_OPTS = --dbname=$(PL_TESTDB) REGRESS = plpgsql_array plpgsql_cache plpgsql_call plpgsql_control \ plpgsql_copy plpgsql_domain plpgsql_misc \ plpgsql_record plpgsql_simple plpgsql_transaction \ - plpgsql_trap plpgsql_trigger plpgsql_varprops + plpgsql_trap plpgsql_trigger plpgsql_varprops plpgsql_foreach # where to find gen_keywordlist.pl and subsidiary files TOOLSDIR = $(top_srcdir)/src/tools diff --git a/src/pl/plpgsql/src/expected/plpgsql_foreach.out b/src/pl/plpgsql/src/expected/plpgsql_foreach.out new file mode 100644 index 00000000000..bc366e78df3 --- /dev/null +++ b/src/pl/plpgsql/src/expected/plpgsql_foreach.out @@ -0,0 +1,192 @@ +-- numeric to numeric +do $$ +declare x numeric; +begin + foreach x in json array '[10,20,30,3.14, null]' + loop + raise notice '%', x; + end loop; +end; +$$; +NOTICE: 10 +NOTICE: 20 +NOTICE: 30 +NOTICE: 3.14 +NOTICE: <NULL> +-- numeric to int by cast +do $$ +declare x int; +begin + foreach x in json array '[10,20,30,3.14, null]' + loop + raise notice '%', x; + end loop; +end; +$$; +NOTICE: 10 +NOTICE: 20 +NOTICE: 30 +NOTICE: 3 +NOTICE: <NULL> +-- conversion "3.14" to int should to fail due IO cast +do $$ +declare x int; +begin + foreach x in json array '["10",20,30,"3.14"]' + loop + raise notice '%', x; + end loop; +end; +$$; +NOTICE: 10 +NOTICE: 20 +NOTICE: 30 +ERROR: invalid input syntax for type integer: "3.14" +CONTEXT: PL/pgSQL function inline_code_block line 4 at FOREACH over json array +do $$ +declare x boolean; +begin + foreach x in json array '[true, false]' + loop + if x then + raise notice 'true'; + else + raise notice 'false'; + end if; + end loop; +end; +$$; +NOTICE: true +NOTICE: false +-- jsonb to jsonb +do $$ +declare x jsonb; +begin + foreach x in json array '[10,20,30,3.14, null, "Hi"]' + loop + raise notice '%', x; + end loop; +end; +$$; +NOTICE: 10 +NOTICE: 20 +NOTICE: 30 +NOTICE: 3.14 +NOTICE: null +NOTICE: "Hi" +-- jsonb to json +do $$ +declare x json; +begin + foreach x in json array '[10,20,30,3.14, null, "Hi"]' + loop + raise notice '%', x; + end loop; +end; +$$; +NOTICE: 10 +NOTICE: 20 +NOTICE: 30 +NOTICE: 3.14 +NOTICE: null +NOTICE: "Hi" +-- iteration over composites +do $$ +declare x int; y numeric; z varchar; +begin + foreach x, y, z in json array '[{}, {"z":"Hi"}, {"y": 3.14}, {"z":"Hi", "x":10, "y":3.14}]' + loop + raise notice 'x: %, y: %, z: %', x, y, z; + end loop; +end; +$$; +NOTICE: x: <NULL>, y: <NULL>, z: <NULL> +NOTICE: x: <NULL>, y: <NULL>, z: Hi +NOTICE: x: <NULL>, y: 3.14, z: <NULL> +NOTICE: x: 10, y: 3.14, z: Hi +create type t3 as (x int, y numeric, z varchar); +do $$ +declare c t3; +begin + foreach c in json array '[{}, {"z":"Hi"}, {"y": 3.14}, {"z":"Hi", "x":10, "y":3.14}]' + loop + raise notice 'x: %, y: %, z: %', c.x, c.y, c.z; + end loop; +end; +$$; +NOTICE: x: <NULL>, y: <NULL>, z: <NULL> +NOTICE: x: <NULL>, y: <NULL>, z: Hi +NOTICE: x: <NULL>, y: 3.14, z: <NULL> +NOTICE: x: 10, y: 3.14, z: Hi +do $$ +declare c t3; +begin + foreach c.x, c.y, c.z in json array '[{}, {"z":"Hi"}, {"y": 3.14}, {"z":"Hi", "x":10, "y":3.14}]' + loop + raise notice 'x: %, y: %, z: %', c.x, c.y, c.z; + end loop; +end; +$$; +NOTICE: x: <NULL>, y: <NULL>, z: <NULL> +NOTICE: x: <NULL>, y: <NULL>, z: Hi +NOTICE: x: <NULL>, y: 3.14, z: <NULL> +NOTICE: x: 10, y: 3.14, z: Hi +drop type t3; +-- target can be a array +do $$ +declare x int[]; +begin + foreach x in json array '[[1,2,3],[4,5,6]]' + loop + raise notice '% % %', x[1], x[2], x[3]; + end loop; +end; +$$; +NOTICE: 1 2 3 +NOTICE: 4 5 6 +do $$ +declare x varchar[]; +begin + foreach x in json array '[["Hi","Hello"],["Hello","Hi"]]' + loop + raise notice '% %', x[1], x[2]; + end loop; +end; +$$; +NOTICE: Hi Hello +NOTICE: Hello Hi +do $$ +declare x varchar[]; +begin + foreach x in json array '[["Hi","Hello"],["Hello","Hi"]]' + loop + raise notice '% %', x[1], x[2]; + end loop; +end; +$$; +NOTICE: Hi Hello +NOTICE: Hello Hi +do $$ +declare x int[]; y varchar; +begin + foreach x, y in json array '[{"x":[1,2,3], "y":"Hi"}, {"x":[4,5,6], "y":"Hi"}]' + loop + raise notice '% % %, y: %', x[1], x[2], x[3], y; + end loop; +end; +$$; +NOTICE: 1 2 3, y: Hi +NOTICE: 4 5 6, y: Hi +create type t2 as (x int[], y varchar); +do $$ +declare c t2; +begin + foreach c in json array '[{"x":[1,2,3], "y":"Hi"}, {"x":[4,5,6], "y":"Hi"}]' + loop + raise notice '% % %, y: %', c.x[1], c.x[2], c.x[3], c.y; + end loop; +end; +$$; +NOTICE: 1 2 3, y: Hi +NOTICE: 4 5 6, y: Hi +drop type t2; diff --git a/src/pl/plpgsql/src/meson.build b/src/pl/plpgsql/src/meson.build index 6ff27006cfc..609eed7a28d 100644 --- a/src/pl/plpgsql/src/meson.build +++ b/src/pl/plpgsql/src/meson.build @@ -88,6 +88,7 @@ tests += { 'plpgsql_trap', 'plpgsql_trigger', 'plpgsql_varprops', + 'plpgsql_foreach', ], }, } diff --git a/src/pl/plpgsql/src/pl_exec.c b/src/pl/plpgsql/src/pl_exec.c index 84552e32c87..f988a1d74c4 100644 --- a/src/pl/plpgsql/src/pl_exec.c +++ b/src/pl/plpgsql/src/pl_exec.c @@ -41,6 +41,8 @@ #include "utils/builtins.h" #include "utils/datum.h" #include "utils/fmgroids.h" +#include "utils/jsonb.h" +#include "utils/jsonfuncs.h" #include "utils/lsyscache.h" #include "utils/memutils.h" #include "utils/rel.h" @@ -305,6 +307,8 @@ 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_foreach_json_a(PLpgSQL_execstate *estate, + PLpgSQL_stmt_foreach_json_a *stmt); static int exec_stmt_open(PLpgSQL_execstate *estate, PLpgSQL_stmt_open *stmt); static int exec_stmt_fetch(PLpgSQL_execstate *estate, @@ -2075,6 +2079,10 @@ exec_stmts(PLpgSQL_execstate *estate, List *stmts) rc = exec_stmt_foreach_a(estate, (PLpgSQL_stmt_foreach_a *) stmt); break; + case PLPGSQL_STMT_FOREACH_JSON_A: + rc = exec_stmt_foreach_json_a(estate, (PLpgSQL_stmt_foreach_json_a *) stmt); + break; + case PLPGSQL_STMT_EXIT: rc = exec_stmt_exit(estate, (PLpgSQL_stmt_exit *) stmt); break; @@ -2995,6 +3003,240 @@ exec_stmt_forc(PLpgSQL_execstate *estate, PLpgSQL_stmt_forc *stmt) } +/* + * Convert JsonbValue to Datum that can be assigned to PLpgSQL_var. + */ +static Datum +JsonbValueToDatum(JsonbValue *jbv, + Oid *typid, int32 *typmod, bool *isnull, + Oid expected_typid, int32 expected_typmod, + void **cache, MemoryContext mcxt) +{ + if (expected_typid == JSONBOID) + { + *typid = JSONBOID; + *typmod = -1; + *isnull = false; + + return PointerGetDatum(JsonbValueToJsonb(jbv)); + } + else if (expected_typid == JSONOID) + { + Jsonb *jsonb; + char *str; + + /* serialize JsonValue to JSON text */ + jsonb = JsonbValueToJsonb(jbv); + str = JsonbToCString(NULL, &jsonb->root, VARSIZE(jsonb)); + + *typid = TEXTOID; + *typmod = -1; + *isnull = false; + + return PointerGetDatum(cstring_to_text(str)); + } + else if (jbv->type == jbvNull) + { + *typid = expected_typid; + *typmod = -1; + *isnull = true; + + return (Datum) 0; + } + else if (jbv->type == jbvString) + { + *typid = TEXTOID; + *typmod = -1; + *isnull = false; + + return PointerGetDatum(cstring_to_text_with_len(jbv->val.string.val, + jbv->val.string.len)); + } + else if (jbv->type == jbvNumeric) + { + *typid = NUMERICOID; + *typmod = -1; + *isnull = false; + + return PointerGetDatum(jbv->val.numeric); + } + else if (jbv->type == jbvBool) + { + *typid = BOOLOID; + *typmod = -1; + *isnull = false; + + return BoolGetDatum(jbv->val.boolean); + } + else + { + Jsonb *jsonb; + Datum result; + + jsonb = JsonbValueToJsonb(jbv); + result = json_populate_type(PointerGetDatum(jsonb), JSONBOID, + expected_typid, expected_typmod, + cache, mcxt, + isnull, false, NULL); + + *typid = expected_typid; + *typmod = expected_typmod; + + return result; + } +} + +/* ---------- + * exec_stmt_foreach_json_a Loop over elements in json array + * + * When target is a composite, then target is populated like json_to_populate_record. + * jsonb doesn't preserve attribute order, so position based mapping between + * target and source can be possibly dangerous (with unexpected behave). + * ---------- + */ +static int +exec_stmt_foreach_json_a(PLpgSQL_execstate *estate, + PLpgSQL_stmt_foreach_json_a *stmt) +{ + Oid exprtypeid; + int32 exprtypmod; + Datum exprdatum; + PLpgSQL_datum *loop_var; + Oid loop_var_typid; + int32 loop_var_typmod; + Oid loop_var_collation; + Jsonb *jb; + JsonbIterator *it; + JsonbValue jbv; + JsonbIteratorToken r; + MemoryContext stmt_mcontext; + MemoryContext oldcontext; + MemoryContext tmp_cxt; + bool found = false; + bool isnull; + bool skipNested = false; + int rc = PLPGSQL_RC_OK; + void *cache = NULL; + + /* get the value of the expression */ + exprdatum = exec_eval_expr(estate, stmt->expr, &isnull, + &exprtypeid, &exprtypmod); + if (isnull) + ereport(ERROR, + (errcode(ERRCODE_NULL_VALUE_NOT_ALLOWED), + errmsg("FOREACH expression must not be null"))); + + /* + * Do as much as possible of the code below in stmt_mcontext, to avoid any + * leaks from called subroutines. We need a private stmt_mcontext since + * we'll be calling arbitrary statement code. + */ + stmt_mcontext = get_stmt_mcontext(estate); + push_stmt_mcontext(estate); + oldcontext = MemoryContextSwitchTo(stmt_mcontext); + + tmp_cxt = AllocSetContextCreate(CurrentMemoryContext, + "FOREACH IN JSON ARRAY temporary cxt", + ALLOCSET_DEFAULT_SIZES); + + /* cast to jsonb */ + exprdatum = exec_cast_value(estate, exprdatum, &isnull, + exprtypeid, exprtypmod, + JSONBOID, -1); + + Assert(!isnull); + + /* + * We must copy the array into stmt_mcontext, 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. + */ + jb = DatumGetJsonbPCopy(exprdatum); + + /* Clean up any leftover temporary memory */ + exec_eval_cleanup(estate); + + /* + * This is compatible with jsonb_array_element. SQL/JSON functions are not + * too strict like PostgreSQL proprietary (old json) functions. In SQL/JSON + * a scalar is equal to one element array. The basic question is if FOREACH + * should be more restrictive like old JSON function, or less restrictive + * like SQL/JSON functions. + */ + if (JB_ROOT_IS_SCALAR(jb)) + ereport(ERROR, + (errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("cannot extract elements from a scalar"))); + else if (!JB_ROOT_IS_ARRAY(jb)) + ereport(ERROR, + (errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("cannot extract elements from an object"))); + + /* Set up the loop variable and see if it is of an array type */ + loop_var = estate->datums[stmt->varno]; + + plpgsql_exec_get_datum_type_info(estate, loop_var, + &loop_var_typid, &loop_var_typmod, + &loop_var_collation); + + it = JsonbIteratorInit(&jb->root); + + while ((r = JsonbIteratorNext(&it, &jbv, skipNested)) != WJB_DONE) + { + skipNested = true; + + if (r == WJB_ELEM) + { + Datum val; + Oid valtypid; + int32 valtypmod; + bool valisnull; + + MemoryContextSwitchTo(tmp_cxt); + + val = JsonbValueToDatum(&jbv, + &valtypid, &valtypmod, &valisnull, + loop_var_typid, loop_var_typmod, + &cache, stmt_mcontext); + + /* exec_assign_value and exec_stmts must run in the main context */ + MemoryContextSwitchTo(oldcontext); + + /* Assign current element/slice to the loop variable */ + exec_assign_value(estate, loop_var, val, + valisnull, valtypid, valtypmod); + + MemoryContextReset(tmp_cxt); + + /* + * Execute the statements + */ + rc = exec_stmts(estate, stmt->body); + + LOOP_RC_PROCESSING(stmt->label, break); + + MemoryContextSwitchTo(stmt_mcontext); + } + } + + /* Restore memory context state */ + MemoryContextSwitchTo(oldcontext); + pop_stmt_mcontext(estate); + + /* Release temporary memory, including the array value */ + MemoryContextReset(stmt_mcontext); + + /* + * 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_foreach_a Loop over elements or slices of an array * @@ -5537,6 +5779,51 @@ plpgsql_exec_get_datum_type_info(PLpgSQL_execstate *estate, break; } + case PLPGSQL_DTYPE_ROW: + { + PLpgSQL_row *row = (PLpgSQL_row *) datum; + + if (!row->rowtupdesc) + { + int i; + + row->rowtupdesc = CreateTemplateTupleDesc(row->nfields); + + for (i = 0; i < row->nfields; i++) + { + PLpgSQL_datum *var = estate->datums[row->varnos[i]]; + Oid vartypid; + int32 vartypmod; + Oid varcollation; + + /* + * We cannot to use fieldnames for tupdescentry, because + * these names can be suffixed by name of row variable. + * Unfortunately, the PLpgSQL_recfield is not casted to + * PLpgSQL_variable. + */ + plpgsql_exec_get_datum_type_info(estate, var, + &vartypid, &vartypmod, + &varcollation); + + TupleDescInitEntry(row->rowtupdesc, i + 1, + var->refname, vartypid, vartypmod, + 0); + TupleDescInitEntryCollation(row->rowtupdesc, i + 1, + varcollation); + } + + /* Make sure we have a valid type/typmod setting */ + BlessTupleDesc(row->rowtupdesc); + } + + *typeId = row->rowtupdesc->tdtypeid; + *typMod = row->rowtupdesc->tdtypmod; + /* composite types are never collatable */ + *collation = InvalidOid; + break; + } + case PLPGSQL_DTYPE_REC: { PLpgSQL_rec *rec = (PLpgSQL_rec *) datum; diff --git a/src/pl/plpgsql/src/pl_funcs.c b/src/pl/plpgsql/src/pl_funcs.c index 92cd9116c0e..7511fab7e68 100644 --- a/src/pl/plpgsql/src/pl_funcs.c +++ b/src/pl/plpgsql/src/pl_funcs.c @@ -253,6 +253,8 @@ plpgsql_stmt_typename(PLpgSQL_stmt *stmt) return _("FOR over cursor"); case PLPGSQL_STMT_FOREACH_A: return _("FOREACH over array"); + case PLPGSQL_STMT_FOREACH_JSON_A: + return _("FOREACH over json array"); case PLPGSQL_STMT_EXIT: return ((PLpgSQL_stmt_exit *) stmt)->is_exit ? "EXIT" : "CONTINUE"; case PLPGSQL_STMT_RETURN: @@ -467,6 +469,14 @@ plpgsql_statement_tree_walker_impl(PLpgSQL_stmt *stmt, { PLpgSQL_stmt_foreach_a *fstmt = (PLpgSQL_stmt_foreach_a *) stmt; + E_WALK(fstmt->expr); + S_LIST_WALK(fstmt->body); + break; + } + case PLPGSQL_STMT_FOREACH_JSON_A: + { + PLpgSQL_stmt_foreach_json_a *fstmt = (PLpgSQL_stmt_foreach_json_a *) stmt; + E_WALK(fstmt->expr); S_LIST_WALK(fstmt->body); break; @@ -795,6 +805,7 @@ 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_foreach_json_a(PLpgSQL_stmt_foreach_json_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); @@ -861,6 +872,9 @@ dump_stmt(PLpgSQL_stmt *stmt) case PLPGSQL_STMT_FOREACH_A: dump_foreach_a((PLpgSQL_stmt_foreach_a *) stmt); break; + case PLPGSQL_STMT_FOREACH_JSON_A: + dump_foreach_json_a((PLpgSQL_stmt_foreach_json_a *) stmt); + break; case PLPGSQL_STMT_EXIT: dump_exit((PLpgSQL_stmt_exit *) stmt); break; @@ -1157,6 +1171,21 @@ dump_foreach_a(PLpgSQL_stmt_foreach_a *stmt) printf(" ENDFOREACHA"); } +static void +dump_foreach_json_a(PLpgSQL_stmt_foreach_json_a *stmt) +{ + dump_ind(); + printf("FOREACHA var %d ", stmt->varno); + printf("IN JSON ARRAY "); + dump_expr(stmt->expr); + printf("\n"); + + dump_stmts(stmt->body); + + dump_ind(); + printf(" ENDFOREACHA"); +} + static void dump_open(PLpgSQL_stmt_open *stmt) { diff --git a/src/pl/plpgsql/src/pl_gram.y b/src/pl/plpgsql/src/pl_gram.y index 5009e59a78f..23b465b10d5 100644 --- a/src/pl/plpgsql/src/pl_gram.y +++ b/src/pl/plpgsql/src/pl_gram.y @@ -178,6 +178,7 @@ static void check_raise_parameters(PLpgSQL_stmt_raise *stmt); PLpgSQL_diag_item *diagitem; PLpgSQL_stmt_fetch *fetch; PLpgSQL_case_when *casewhen; + PLpgSQL_stmt_foreach *foreach; } %type <declhdr> decl_sect @@ -220,6 +221,7 @@ static void check_raise_parameters(PLpgSQL_stmt_raise *stmt); %type <casewhen> case_when %type <list> case_when_list opt_case_else +%type <foreach> foreach_type %type <boolean> getdiag_area_opt %type <list> getdiag_list @@ -341,6 +343,7 @@ static void check_raise_parameters(PLpgSQL_stmt_raise *stmt); %token <keyword> K_PRINT_STRICT_PARAMS %token <keyword> K_PRIOR %token <keyword> K_QUERY +%token <keyword> K_JSON %token <keyword> K_RAISE %token <keyword> K_RELATIVE %token <keyword> K_RETURN @@ -1671,16 +1674,29 @@ for_variable : T_DATUM } ; -stmt_foreach_a : opt_loop_label K_FOREACH for_variable foreach_slice K_IN K_ARRAY expr_until_loop loop_body +stmt_foreach_a : opt_loop_label K_FOREACH for_variable foreach_slice K_IN foreach_type expr_until_loop loop_body { - PLpgSQL_stmt_foreach_a *new; + PLpgSQL_stmt_foreach *new; - new = palloc0_object(PLpgSQL_stmt_foreach_a); - new->cmd_type = PLPGSQL_STMT_FOREACH_A; + new = $6; new->lineno = plpgsql_location_to_lineno(@2, yyscanner); new->stmtid = ++plpgsql_curr_compile->nstatements; new->label = $1; - new->slice = $4; + + if ($4 > 0) + { + /* slicing is supported only by FOREACH IN ARRAY */ + if (new->cmd_type == PLPGSQL_STMT_FOREACH_A) + { + ((PLpgSQL_stmt_foreach_a *) new)->slice = $4; + } + else + ereport(ERROR, + (errcode(ERRCODE_SYNTAX_ERROR), + errmsg("not zero slice is allowed only for arrays"), + parser_errposition(@4))); + } + new->expr = $7; new->body = $8.stmts; @@ -1719,6 +1735,19 @@ foreach_slice : } ; +foreach_type : + K_ARRAY + { + $$ = (PLpgSQL_stmt_foreach *) palloc0_object(PLpgSQL_stmt_foreach_a); + $$->cmd_type = PLPGSQL_STMT_FOREACH_A; + } + | K_JSON K_ARRAY + { + $$ = (PLpgSQL_stmt_foreach *) palloc0_object(PLpgSQL_stmt_foreach_json_a); + $$->cmd_type = PLPGSQL_STMT_FOREACH_JSON_A; + } + ; + stmt_exit : exit_type opt_label opt_exitcond { PLpgSQL_stmt_exit *new; diff --git a/src/pl/plpgsql/src/pl_unreserved_kwlist.h b/src/pl/plpgsql/src/pl_unreserved_kwlist.h index 6379e86c8cb..d7588d3b4ad 100644 --- a/src/pl/plpgsql/src/pl_unreserved_kwlist.h +++ b/src/pl/plpgsql/src/pl_unreserved_kwlist.h @@ -69,6 +69,7 @@ PG_KEYWORD("import", K_IMPORT) PG_KEYWORD("info", K_INFO) PG_KEYWORD("insert", K_INSERT) PG_KEYWORD("is", K_IS) +PG_KEYWORD("json", K_JSON) PG_KEYWORD("last", K_LAST) PG_KEYWORD("log", K_LOG) PG_KEYWORD("merge", K_MERGE) diff --git a/src/pl/plpgsql/src/plpgsql.h b/src/pl/plpgsql/src/plpgsql.h index addb14a9959..c57b1da9b95 100644 --- a/src/pl/plpgsql/src/plpgsql.h +++ b/src/pl/plpgsql/src/plpgsql.h @@ -112,6 +112,7 @@ typedef enum PLpgSQL_stmt_type PLPGSQL_STMT_FORS, PLPGSQL_STMT_FORC, PLPGSQL_STMT_FOREACH_A, + PLPGSQL_STMT_FOREACH_JSON_A, PLPGSQL_STMT_EXIT, PLPGSQL_STMT_RETURN, PLPGSQL_STMT_RETURN_NEXT, @@ -299,6 +300,7 @@ typedef struct PLpgSQL_datum { PLpgSQL_datum_type dtype; int dno; + char *refname; } PLpgSQL_datum; /* @@ -444,9 +446,9 @@ typedef struct PLpgSQL_recfield { PLpgSQL_datum_type dtype; int dno; + char *fieldname; /* name of field */ /* end of PLpgSQL_datum fields */ - char *fieldname; /* name of field */ int recparentno; /* dno of parent record */ int nextfield; /* dno of next child, or -1 if none */ uint64 rectupledescid; /* record's tupledesc ID as of last lookup */ @@ -766,6 +768,20 @@ typedef struct PLpgSQL_stmt_dynfors List *params; /* USING expressions */ } PLpgSQL_stmt_dynfors; +/* + * FOREACH loop (ancestor IN ARRAY and IN JSON ARRAY loop) + */ +typedef struct PLpgSQL_stmt_foreach +{ + PLpgSQL_stmt_type cmd_type; + int lineno; + unsigned int stmtid; + char *label; + int varno; /* loop target variable */ + PLpgSQL_expr *expr; /* set expression */ + List *body; /* List of statements */ +} PLpgSQL_stmt_foreach; + /* * FOREACH item in array loop */ @@ -776,11 +792,27 @@ typedef struct PLpgSQL_stmt_foreach_a unsigned int stmtid; char *label; int varno; /* loop target variable */ - int slice; /* slice dimension, or 0 */ PLpgSQL_expr *expr; /* array expression */ List *body; /* List of statements */ + /* end of fields that must match PLpgSQL_stmt_foreach */ + int slice; /* slice dimension, or 0 */ } PLpgSQL_stmt_foreach_a; +/* + * FOREACH item in array loop + */ +typedef struct PLpgSQL_stmt_foreach_json_a +{ + PLpgSQL_stmt_type cmd_type; + int lineno; + unsigned int stmtid; + char *label; + int varno; /* loop target variable */ + PLpgSQL_expr *expr; /* array expression */ + List *body; /* List of statements */ + /* end of fields that must match PLpgSQL_stmt_foreach */ +} PLpgSQL_stmt_foreach_json_a; + /* * OPEN a curvar */ diff --git a/src/pl/plpgsql/src/sql/plpgsql_foreach.sql b/src/pl/plpgsql/src/sql/plpgsql_foreach.sql new file mode 100644 index 00000000000..33acb8f924c --- /dev/null +++ b/src/pl/plpgsql/src/sql/plpgsql_foreach.sql @@ -0,0 +1,159 @@ +-- numeric to numeric +do $$ +declare x numeric; +begin + foreach x in json array '[10,20,30,3.14, null]' + loop + raise notice '%', x; + end loop; +end; +$$; + +-- numeric to int by cast +do $$ +declare x int; +begin + foreach x in json array '[10,20,30,3.14, null]' + loop + raise notice '%', x; + end loop; +end; +$$; + +-- conversion "3.14" to int should to fail due IO cast +do $$ +declare x int; +begin + foreach x in json array '["10",20,30,"3.14"]' + loop + raise notice '%', x; + end loop; +end; +$$; + +do $$ +declare x boolean; +begin + foreach x in json array '[true, false]' + loop + if x then + raise notice 'true'; + else + raise notice 'false'; + end if; + end loop; +end; +$$; + +-- jsonb to jsonb +do $$ +declare x jsonb; +begin + foreach x in json array '[10,20,30,3.14, null, "Hi"]' + loop + raise notice '%', x; + end loop; +end; +$$; + +-- jsonb to json +do $$ +declare x json; +begin + foreach x in json array '[10,20,30,3.14, null, "Hi"]' + loop + raise notice '%', x; + end loop; +end; +$$; + +-- iteration over composites +do $$ +declare x int; y numeric; z varchar; +begin + foreach x, y, z in json array '[{}, {"z":"Hi"}, {"y": 3.14}, {"z":"Hi", "x":10, "y":3.14}]' + loop + raise notice 'x: %, y: %, z: %', x, y, z; + end loop; +end; +$$; + +create type t3 as (x int, y numeric, z varchar); + +do $$ +declare c t3; +begin + foreach c in json array '[{}, {"z":"Hi"}, {"y": 3.14}, {"z":"Hi", "x":10, "y":3.14}]' + loop + raise notice 'x: %, y: %, z: %', c.x, c.y, c.z; + end loop; +end; +$$; + +do $$ +declare c t3; +begin + foreach c.x, c.y, c.z in json array '[{}, {"z":"Hi"}, {"y": 3.14}, {"z":"Hi", "x":10, "y":3.14}]' + loop + raise notice 'x: %, y: %, z: %', c.x, c.y, c.z; + end loop; +end; +$$; + +drop type t3; + +-- target can be a array +do $$ +declare x int[]; +begin + foreach x in json array '[[1,2,3],[4,5,6]]' + loop + raise notice '% % %', x[1], x[2], x[3]; + end loop; +end; +$$; + +do $$ +declare x varchar[]; +begin + foreach x in json array '[["Hi","Hello"],["Hello","Hi"]]' + loop + raise notice '% %', x[1], x[2]; + end loop; +end; +$$; + +do $$ +declare x varchar[]; +begin + foreach x in json array '[["Hi","Hello"],["Hello","Hi"]]' + loop + raise notice '% %', x[1], x[2]; + end loop; +end; +$$; + + +do $$ +declare x int[]; y varchar; +begin + foreach x, y in json array '[{"x":[1,2,3], "y":"Hi"}, {"x":[4,5,6], "y":"Hi"}]' + loop + raise notice '% % %, y: %', x[1], x[2], x[3], y; + end loop; +end; +$$; + +create type t2 as (x int[], y varchar); + +do $$ +declare c t2; +begin + foreach c in json array '[{"x":[1,2,3], "y":"Hi"}, {"x":[4,5,6], "y":"Hi"}]' + loop + raise notice '% % %, y: %', c.x[1], c.x[2], c.x[3], c.y; + end loop; +end; +$$; + +drop type t2; -- 2.53.0
