Hi
út 3. 3. 2026 v 8:43 odesílatel Jim Jones <[email protected]>
napsal:
> Hi Pavel,
>
> I quickly tested the patch, and I also could observe a ~3x performance
> improvement!
>
> A few first impressions:
>
> ## in exec_stmt_foreach_json_a the boolean variable found is declared as
> false, bit its value is never set to true until exec_set_found() is called:
>
> /*
> * 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);
>
>
> Test:
>
>
> DO $$
> DECLARE
> x int;
> BEGIN
> FOREACH x IN JSON ARRAY '[1,2,3]'
> LOOP
> RAISE NOTICE 'x: %', x;
> END LOOP;
>
> IF FOUND THEN
> RAISE NOTICE 'FOUND is true';
> ELSE
> RAISE NOTICE 'FOUND is false';
> END IF;
> END;
> $$;
> NOTICE: x: 1
> NOTICE: x: 2
> NOTICE: x: 3
> NOTICE: FOUND is false
>
>
fixed + regress tests
>
> ## Suggestion in the plpgsql.sgml
>
> The <literal>FOREACH</literal> loop is much like a
> <literal>FOREACH</literal> loop,
>
> to
>
> "much like a regular <literal>FOREACH</literal> loop over arrays"
>
done
>
> ## Typo in comment
>
> /*
> * We cannot to use fieldnames for tupdescentry, because
> * these names can be suffixed by name of row variable.
> ...
>
> We cannot to use > We cannot use
>
fixed
>
>
> ## Nit pick
>
> These error messages are not wrong, but IMO a errhint/errdetail could
> add some value here:
>
> ereport(ERROR,
> (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
> errmsg("cannot extract elements from a scalar")));
>
> ereport(ERROR,
> (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
> errmsg("cannot extract elements from an object")));
>
> Something like this perhaps?
>
> ereport(ERROR,
> (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
> errmsg("cannot extract elements from a scalar"),
> errhint("FOREACH IN JSON ARRAY requires an array value.")));
>
> ereport(ERROR,
> (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
> errmsg("FOREACH expression must evaluate to a JSON array"),
> errdetail("Cannot iterate over a scalar value.")));
>
>
I rewrote it to
if (JB_ROOT_IS_SCALAR(jb))
ereport(ERROR,
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
errmsg("FOREACH expression must evaluate to a JSON array"),
errhint("Cannot iterate over a scalar value.")));
else if (JB_ROOT_IS_OBJECT(jb))
ereport(ERROR,
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
errmsg("FOREACH expression must evaluate to a JSON array"),
errdetail("Cannot iterate over a object value.")));
Assert(JB_ROOT_IS_ARRAY(jb));
+ regress tests
>
> Thanks for the patch!
>
Thank you for check
Regards
Pavel
>
> Best, Jim
>
From 899ee1689d9dc537c57a8e74e04088d638cc3ccc 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 | 60 ++++
src/pl/plpgsql/src/Makefile | 2 +-
.../plpgsql/src/expected/plpgsql_foreach.out | 253 +++++++++++++++
src/pl/plpgsql/src/meson.build | 1 +
src/pl/plpgsql/src/pl_exec.c | 296 ++++++++++++++++++
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 | 214 +++++++++++++
10 files changed, 923 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..ad12d2191aa 100644
--- a/doc/src/sgml/plpgsql.sgml
+++ b/doc/src/sgml/plpgsql.sgml
@@ -2780,6 +2780,66 @@ 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 regular
+ <literal>FOREACH</literal> loop over arrays,
+ 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..b24e73277f1
--- /dev/null
+++ b/src/pl/plpgsql/src/expected/plpgsql_foreach.out
@@ -0,0 +1,253 @@
+-- input must be a JSON array
+do $$
+declare x numeric;
+begin
+ foreach x in json array NULL -- fail
+ loop
+ raise notice '%', x;
+ end loop;
+end;
+$$;
+ERROR: FOREACH expression must not be null
+CONTEXT: PL/pgSQL function inline_code_block line 4 at FOREACH over json array
+do $$
+declare x numeric;
+begin
+ foreach x in json array '10' -- fail
+ loop
+ raise notice '%', x;
+ end loop;
+end;
+$$;
+ERROR: FOREACH expression must evaluate to a JSON array
+HINT: Cannot iterate over a scalar value.
+CONTEXT: PL/pgSQL function inline_code_block line 4 at FOREACH over json array
+do $$
+declare x numeric;
+begin
+ foreach x in json array '{}' -- fail
+ loop
+ raise notice '%', x;
+ end loop;
+end;
+$$;
+ERROR: FOREACH expression must evaluate to a JSON array
+DETAIL: Cannot iterate over a object value.
+CONTEXT: PL/pgSQL function inline_code_block line 4 at FOREACH over json array
+-- 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>
+-- test of FOUND variable
+do $$
+declare x int;
+begin
+ foreach x in json array '[10]'
+ loop
+ raise notice '%', x;
+ end loop;
+ raise notice 'FOUND: %', found;
+end;
+$$;
+NOTICE: 10
+NOTICE: FOUND: t
+-- test of FOUND variable
+do $$
+declare x int;
+begin
+ foreach x in json array '[]'
+ loop
+ raise notice '%', x;
+ end loop;
+ raise notice 'FOUND: %', found;
+end;
+$$;
+NOTICE: FOUND: f
+-- 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..036390f07a4 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,249 @@ 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;
+
+ /* isnull in json_populate_type is inout argument */
+ *isnull = false;
+
+ 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("FOREACH expression must evaluate to a JSON array"),
+ errhint("Cannot iterate over a scalar value.")));
+ else if (JB_ROOT_IS_OBJECT(jb))
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("FOREACH expression must evaluate to a JSON array"),
+ errdetail("Cannot iterate over a object value.")));
+
+ Assert(JB_ROOT_IS_ARRAY(jb));
+
+ /* 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;
+
+ found = true; /* looped at least once */
+
+ 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 +5788,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 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..0e5c7106d1e
--- /dev/null
+++ b/src/pl/plpgsql/src/sql/plpgsql_foreach.sql
@@ -0,0 +1,214 @@
+-- input must be a JSON array
+do $$
+declare x numeric;
+begin
+ foreach x in json array NULL -- fail
+ loop
+ raise notice '%', x;
+ end loop;
+end;
+$$;
+
+do $$
+declare x numeric;
+begin
+ foreach x in json array '10' -- fail
+ loop
+ raise notice '%', x;
+ end loop;
+end;
+$$;
+
+do $$
+declare x numeric;
+begin
+ foreach x in json array '{}' -- fail
+ loop
+ raise notice '%', x;
+ end loop;
+end;
+$$;
+
+-- 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;
+$$;
+
+-- test of FOUND variable
+do $$
+declare x int;
+begin
+ foreach x in json array '[10]'
+ loop
+ raise notice '%', x;
+ end loop;
+ raise notice 'FOUND: %', found;
+end;
+$$;
+
+-- test of FOUND variable
+do $$
+declare x int;
+begin
+ foreach x in json array '[]'
+ loop
+ raise notice '%', x;
+ end loop;
+ raise notice 'FOUND: %', found;
+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
