Hi
I wrote PoC for previously proposed plpgsql statement FOREACH IN JSON ARRAY
It looks like:
do $$
declare x int;
begin
foreach x in json array '[1,2,3,4]'
loop
raise notice 'x: %', x;
end loop;
end;
$$
do $$
declare x int; y int;
begin
foreach x, y in json array '[{"x": 100, "y": 1000}, {"y": 1000, "x":
100}]'
loop
raise notice 'x: %, y: %', x, y;
end loop;
end
$$
My first motivation for this patch is performance. This is faster (3 - 4 x)
than using FOR IN SELECT FROM json_array_elements, because there is no
overhead of SQL executor. Second motivation is a little bit better
readability, because inside plpgsql' statements we have info about used
variables and we can use it.
The behavior is very similar to FOREACH IN ARRAY with one significant
difference - the values of JSON objects are assigned to the composite
variable or lists of variables by names (not by position). It made this
decision because jsonb doesn't preserve the position of the field in
object, and then assignment based on position cannot work.
The code is relatively short now - about 400 lines +/- and the code is
simple without risks.
There are some open questions - mainly if default mode for mapping json
fields to plpgsql variables should be in lax or strict mode. Now, it is
something between (cast errors are raised) - it is consistent
with jsonb_populate_record - but it should not be the final design. I
cannot say what is better - currently implemented behavior is consistent
with common plpgsql behaviour, but SQL/JSON is different. I can imagine
that default behaviour will be lax, and with some optional clauses we can
push behave to strict mode. I have no strong opinion about it. Maybe I
prefer the current "strict" behaviour a little bit, because it is more
"safe", but it is only my personal opinion. But again, I have no strong
opinion about this question and I very much invite any discussion about it.
This is proof of concept patch - casting between plpgsql arrays and json
arrays is not supported, documentation and regress tests are minimalistic,
but it is good enough for testing and good enough for decision, if this
feature is wanted or not (or if it needs some modifications).
This is a new feature (and proprietary feature). There should not be any
compatibility issues.
What do you think about this feature?
Regards
Pavel
From c9f041e5408351961425eabeef161c4b72b93d1c 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 | 63 ++++
src/backend/utils/adt/jsonb_util.c | 1 +
src/pl/plpgsql/src/Makefile | 2 +-
.../plpgsql/src/expected/plpgsql_foreach.out | 119 +++++++
src/pl/plpgsql/src/meson.build | 1 +
src/pl/plpgsql/src/pl_comp.c | 4 +-
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 | 35 ++-
src/pl/plpgsql/src/sql/plpgsql_foreach.sql | 89 ++++++
12 files changed, 669 insertions(+), 10 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..c11f44676bf 100644
--- a/doc/src/sgml/plpgsql.sgml
+++ b/doc/src/sgml/plpgsql.sgml
@@ -2780,6 +2780,69 @@ 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>
+
+ <para>
+ Assigning to PLpgSQL array variables is not supported.
+ </para>
+ </sect2>
+
<sect2 id="plpgsql-error-trapping">
<title>Trapping Errors</title>
diff --git a/src/backend/utils/adt/jsonb_util.c b/src/backend/utils/adt/jsonb_util.c
index 91fb9ea09bf..7513b1198dd 100644
--- a/src/backend/utils/adt/jsonb_util.c
+++ b/src/backend/utils/adt/jsonb_util.c
@@ -18,6 +18,7 @@
#include "common/hashfn.h"
#include "miscadmin.h"
#include "port/pg_bitutils.h"
+#include "utils/builtins.h"
#include "utils/date.h"
#include "utils/datetime.h"
#include "utils/datum.h"
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..43ae84412f1
--- /dev/null
+++ b/src/pl/plpgsql/src/expected/plpgsql_foreach.out
@@ -0,0 +1,119 @@
+-- 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
+-- 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;
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_comp.c b/src/pl/plpgsql/src/pl_comp.c
index 5ecc7766757..8fa60976cfd 100644
--- a/src/pl/plpgsql/src/pl_comp.c
+++ b/src/pl/plpgsql/src/pl_comp.c
@@ -1253,6 +1253,7 @@ make_datum_param(PLpgSQL_expr *expr, int dno, int location)
PLpgSQL_datum *datum;
Param *param;
MemoryContext oldcontext;
+ char *refname;
/* see comment in resolve_column_ref */
estate = expr->func->cur_estate;
@@ -1273,7 +1274,8 @@ make_datum_param(PLpgSQL_expr *expr, int dno, int location)
datum,
¶m->paramtype,
¶m->paramtypmod,
- ¶m->paramcollid);
+ ¶m->paramcollid,
+ &refname);
param->location = location;
return (Node *) param;
diff --git a/src/pl/plpgsql/src/pl_exec.c b/src/pl/plpgsql/src/pl_exec.c
index 84552e32c87..3383b42c39a 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;
+ char *loop_var_name;
+ 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, &loop_var_name);
+
+ 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
*
@@ -5522,7 +5764,8 @@ plpgsql_exec_get_datum_type(PLpgSQL_execstate *estate,
void
plpgsql_exec_get_datum_type_info(PLpgSQL_execstate *estate,
PLpgSQL_datum *datum,
- Oid *typeId, int32 *typMod, Oid *collation)
+ Oid *typeId, int32 *typMod, Oid *collation,
+ char **refname)
{
switch (datum->dtype)
{
@@ -5534,6 +5777,54 @@ plpgsql_exec_get_datum_type_info(PLpgSQL_execstate *estate,
*typeId = var->datatype->typoid;
*typMod = var->datatype->atttypmod;
*collation = var->datatype->collation;
+ *refname = var->refname;
+ 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;
+ char *varname;
+
+ /*
+ * 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, &varname);
+
+ TupleDescInitEntry(row->rowtupdesc, i + 1,
+ varname, 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;
+ *refname = row->refname;
break;
}
@@ -5556,6 +5847,7 @@ plpgsql_exec_get_datum_type_info(PLpgSQL_execstate *estate,
}
/* composite types are never collatable */
*collation = InvalidOid;
+ *refname = rec->refname;
break;
}
@@ -5593,6 +5885,7 @@ plpgsql_exec_get_datum_type_info(PLpgSQL_execstate *estate,
*typeId = recfield->finfo.ftypeid;
*typMod = recfield->finfo.ftypmod;
*collation = recfield->finfo.fcollation;
+ *refname = recfield->fieldname;
break;
}
@@ -5601,6 +5894,7 @@ plpgsql_exec_get_datum_type_info(PLpgSQL_execstate *estate,
*typeId = InvalidOid; /* keep compiler quiet */
*typMod = -1;
*collation = InvalidOid;
+ *refname = NULL;
break;
}
}
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..4a1e9ba65bc 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,
@@ -766,6 +767,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 +791,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
*/
@@ -1274,7 +1305,7 @@ extern PGDLLEXPORT Oid plpgsql_exec_get_datum_type(PLpgSQL_execstate *estate,
extern void plpgsql_exec_get_datum_type_info(PLpgSQL_execstate *estate,
PLpgSQL_datum *datum,
Oid *typeId, int32 *typMod,
- Oid *collation);
+ Oid *collation, char **refname);
/*
* Functions for namespace handling in pl_funcs.c
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..7e7abfe2568
--- /dev/null
+++ b/src/pl/plpgsql/src/sql/plpgsql_foreach.sql
@@ -0,0 +1,89 @@
+-- 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;
+$$;
+
+-- 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;
--
2.53.0
