Hi st 1. 7. 2026 v 23:40 odesÃlatel Tom Lane <[email protected]> napsal:
> I took a very quick look through the v20260616 patch. > > I think you need to work harder on the separation of knowledge. > In particular I don't like that the type-specific setup routines > (create_foreach_a_array_iterator etc) contain so many assumptions that > they are being called from a PLpgsql FOREACH statement and nothing > else. IMO the point of putting this into the SubscriptRoutines > infrastructure is to make a general-purpose facility that could be > used by different things. So: > > * it's not clear to me that these routines have any business knowing > about the "target type"; they certainly shouldn't contain comments > alluding to PLpgSQL's conversion abilities. I think probably you > just want them to pass back the data type they are producing and > let PLpgSQL decide whether it wants to convert or not. > The iterator should know the target type for more reasons: 1. Only inside the iterator's implementation we can check things related to slicing. PLpgSQL engine cannot have knowledge about slicing on some more generic level. 2. Iterators can be implemented for types that have their own implementation of composite values (jsonb objects). Iterators with knowledge of the target type can do the correct transformation. Now, there are not any generic casts between jsonb and composite types. 3. When iterator knows target type, then it can reduce some unwanted transformations like JSONB->numeric->JSONB or can returns PostgreSQL scalar types like numeric and can change cast from JSON->INT to NUMERIC->INT (explicit casts has same result for numeric input, but implicit casts can produce different results). I dislike the fact, so target types (or expected types) are not enforced. This makes the API dirty. But if I want to enforce cast to expected type inside iterator implementation, then I need to move related code from PL/pgSQL to core - but implemented casts rules are specific for PL/pgSQL only. Attached patch allows to not specify expected_type (use InvalidOid). In this case the behaviour is exactly like you proposed, so maybe it can be a way. > > * we need to think about how the error messages could be phrased more > generically, or else not have these functions throw those errors > themselves but instead pass back an error code that the caller could > use to select an error message. I suspect this will end up with > visible changes in the error messages produced by existing cases, > and that's okay IMO. > I changed messages to be more generic > > * memory management may need to be rethought a bit as well. > Certainly the comments referencing exec_eval_cleanup do not > belong here. > changed Best regards Pavel > > regards, tom lane >
From 297873a5cc8aa6f750b26a06972b564621790917 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 ARRAY jsonb_expr this patch introduce support FOREACH scalar_var IN ARRAY expr, when the result of the expression can be Jsonb. 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 ARRAY '[1,2,3.14]'::jsonb 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"]'::jsonb 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 ARRAY '[1,2,3]' ERROR ON EMPTY ERROR ON ERROR LOOP ... Because we use "old" syntax - FOREACH IN ARRAY, I prefer "old" behaviour, that is more similar to iteration over an array. 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 | 62 +++- src/backend/utils/adt/arraysubs.c | 92 +++++- src/backend/utils/adt/jsonbsubs.c | 196 +++++++++++- src/include/nodes/subscripting.h | 37 +++ src/pl/plpgsql/src/Makefile | 2 +- .../plpgsql/src/expected/plpgsql_foreach.out | 295 ++++++++++++++++++ src/pl/plpgsql/src/meson.build | 1 + src/pl/plpgsql/src/pl_exec.c | 187 +++++------ src/pl/plpgsql/src/plpgsql.h | 3 +- src/pl/plpgsql/src/sql/plpgsql_foreach.sql | 252 +++++++++++++++ 10 files changed, 1033 insertions(+), 94 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..6bc9fb02e1e 100644 --- a/doc/src/sgml/plpgsql.sgml +++ b/doc/src/sgml/plpgsql.sgml @@ -2697,12 +2697,12 @@ END LOOP <optional> <replaceable>label</replaceable> </optional>; </sect2> <sect2 id="plpgsql-foreach-array"> - <title>Looping through Arrays</title> + <title>Looping through Arrays or Jsonb arrays</title> <para> The <literal>FOREACH</literal> loop is much like a <literal>FOR</literal> loop, but instead of iterating through the rows returned by an SQL query, - it iterates through the elements of an array value. + it iterates through the elements of an array value or of jsonb array value. (In general, <literal>FOREACH</literal> is meant for looping through components of a composite-valued expression; variants for looping through composites besides arrays may be added in future.) @@ -2778,6 +2778,64 @@ NOTICE: row = {7,8,9} NOTICE: row = {10,11,12} </programlisting> </para> + + <para> + The <literal>SLICE</literal> higher than zero cannot be used when iterates + through jsonb arrays. + </para> + + <para> + The <literal>FOREACH</literal> loop over jsonb arrays uses + same syntax like <literal>FOREACH</literal> loop over arrays, + but instead of iterating through elements of the array, + it iterates through the elements of a Jsonb array value. + </para> + + <para> + The target can be a scalar variable, a composite variable, or a 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 print_elements(jsonb) RETURNS void AS $$ +DECLARE + x int; +BEGIN + FOREACH x IN ARRAY $1 + LOOP + RAISE NOTICE 'row = %', x; + END LOOP; +END; +$$ LANGUAGE plpgsql; + +SELECT print_elements('[1,2,3]'); +NOTICE: row = 1 +NOTICE: row = 2 +NOTICE: row = 3 + +CREATE FUNCTION print_fields(jsonb) RETURNS void AS $$ +DECLARE + x int; y varchar; +BEGIN + FOREACH x, y IN ARRAY $1 + LOOP + RAISE NOTICE 'x: %, y: %', x, y; + END LOOP; +END; +$$ LANGUAGE plpgsql; + +SELECT print_fields('[{},{"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> + The target variable can be of type <literal>RECORD</literal>, but the real structure has to be + assigned before usage in the <literal>FOREACH</literal> statement. + </para> </sect2> <sect2 id="plpgsql-error-trapping"> diff --git a/src/backend/utils/adt/arraysubs.c b/src/backend/utils/adt/arraysubs.c index 2bf9e9509fb..844ffe92301 100644 --- a/src/backend/utils/adt/arraysubs.c +++ b/src/backend/utils/adt/arraysubs.c @@ -23,6 +23,7 @@ #include "parser/parse_coerce.h" #include "parser/parse_expr.h" #include "utils/array.h" +#include "utils/builtins.h" #include "utils/fmgrprotos.h" #include "utils/lsyscache.h" @@ -46,6 +47,91 @@ typedef struct ArraySubWorkspace int lowerindex[MAXDIM]; } ArraySubWorkspace; +typedef struct +{ + ForeachAIterator pub; + ArrayIterator it; + Oid result_typid; + int32 result_typmod; +} ArrayForeachAIterator; + +static bool +array_foreach_a_iterate(ForeachAIterator *self, + Datum *value, bool *isnull, + Oid *typid, int32 *typmod) +{ + ArrayForeachAIterator *iter = (ArrayForeachAIterator *) self; + + *typid = iter->result_typid; + *typmod = iter->result_typmod; + + return array_iterate(iter->it, value, isnull); +} + +/* + * An array ForeachAIterator constructor + */ +static ForeachAIterator * +create_array_foreach_a_iterator(Datum value, Oid typid, int32 typmod, int slice, + Oid expected_typid, int32 expected_typmod) +{ + ArrayForeachAIterator *iter = palloc0(sizeof(ArrayForeachAIterator)); + ArrayType *arr; + + Assert(OidIsValid(get_element_type(typid))); + + arr = DatumGetArrayTypeP(value); + + /* Slice dimension must be less than or equal to array dimension */ + if (slice < 0 || slice > ARR_NDIM(arr)) + ereport(ERROR, + (errcode(ERRCODE_ARRAY_SUBSCRIPT_ERROR), + errmsg("slice dimension (%d) is out of the valid range 0..%d", + slice, ARR_NDIM(arr)))); + + if (OidIsValid(expected_typid)) + { + Oid target_elem_typid; + + /* + * Sanity-check the expected type. We don't try very hard here, and + * should not be too picky since it's possible that exec_assign_value can + * coerce values of different types. But it seems worthwhile to complain + * if the array-ness of the loop variable is not right. + */ + target_elem_typid = get_element_type(expected_typid); + + if (slice > 0 && target_elem_typid == InvalidOid) + ereport(ERROR, + (errcode(ERRCODE_DATATYPE_MISMATCH), + errmsg("cannot to cast a value of array type to scalar type"))); + if (slice == 0 && target_elem_typid != InvalidOid) + ereport(ERROR, + (errcode(ERRCODE_DATATYPE_MISMATCH), + errmsg("cannot to cast a value of scalar type to array type"))); + } + + /* Identify iterator result type */ + if (slice > 0) + { + /* When slicing, nominal type of result is same as array type */ + iter->result_typid = typid; + iter->result_typmod = typmod; + } + else + { + /* Without slicing, results are individual array elements */ + iter->result_typid = ARR_ELEMTYPE(arr); + iter->result_typmod = typmod; + } + + /* Create an iterator to step through the array */ + iter->it = array_create_iterator(arr, slice, NULL); + + iter->pub.iterate = array_foreach_a_iterate; + + return (ForeachAIterator *) iter; +} /* * Finish parse analysis of a SubscriptingRef expression for an array. @@ -545,7 +631,8 @@ array_subscript_handler(PG_FUNCTION_ARGS) .exec_setup = array_exec_setup, .fetch_strict = true, /* fetch returns NULL for NULL inputs */ .fetch_leakproof = true, /* fetch returns NULL for bad subscript */ - .store_leakproof = false /* ... but assignment throws error */ + .store_leakproof = false, /* ... but assignment throws error */ + .create_foreach_a_iterator = create_array_foreach_a_iterator }; PG_RETURN_POINTER(&sbsroutines); @@ -572,7 +659,8 @@ raw_array_subscript_handler(PG_FUNCTION_ARGS) .exec_setup = array_exec_setup, .fetch_strict = true, /* fetch returns NULL for NULL inputs */ .fetch_leakproof = true, /* fetch returns NULL for bad subscript */ - .store_leakproof = false /* ... but assignment throws error */ + .store_leakproof = false, /* ... but assignment throws error */ + .create_foreach_a_iterator = create_array_foreach_a_iterator }; PG_RETURN_POINTER(&sbsroutines); diff --git a/src/backend/utils/adt/jsonbsubs.c b/src/backend/utils/adt/jsonbsubs.c index f2745b29a3f..e4929b33cec 100644 --- a/src/backend/utils/adt/jsonbsubs.c +++ b/src/backend/utils/adt/jsonbsubs.c @@ -22,6 +22,7 @@ #include "parser/parse_expr.h" #include "utils/builtins.h" #include "utils/jsonb.h" +#include "utils/jsonfuncs.h" /* SubscriptingRefState.workspace for jsonb subscripting execution */ @@ -33,6 +34,17 @@ typedef struct JsonbSubWorkspace Datum *index; /* Subscript values in Datum format */ } JsonbSubWorkspace; +typedef struct +{ + ForeachAIterator pub; + JsonbIterator *it; + bool skip_nested; + Oid expected_typid; + int32 expected_typmod; + + MemoryContext cache_mcxt; + void *cache; +} JsonbForeachAIterator; /* * Finish parse analysis of a SubscriptingRef expression for a jsonb. @@ -394,6 +406,187 @@ jsonb_exec_setup(const SubscriptingRef *sbsref, methods->sbs_fetch_old = jsonb_subscript_fetch_old; } +/* + * Convert JsonbValue to Datum. This function is used by + * generic array iterator. When expected_type is unknown + * or it is json or jsonb, then there are minimal transformations. + * When JsonValue is of basic types (string, numeric, bool), + * then value is converted to most near Postgres native types. + * In other cases, the generic transformation json_populate_type + * is used. + * + * The reason why generic transformation is not used by default + * are different cast rules, because it uses only implicit or + * IO casts, that are usually more restrictive. The cast + * JSONB->INT is more restrictive than casts JSONB->NUMERIC->INT. + * + * The type of result is of expected_typid, expected_typmod only + * when expected_typid is of composite type. Theoretically it can + * be converted to this type in all cases, but it requires more + * significant change of plpgsql code (the cast routines should be + * moved to core), and because (in this moment) only one user of + * generic array iterator is plpgsql FOREACH statement, then the + * cast to expected_type is not enforced here (for all cases). + */ +static Datum +JsonbValueToDatum(JsonbValue *jbv, + Oid *typid, int32 *typmod, bool *isnull, + Oid expected_typid, int32 expected_typmod, + void **cache, MemoryContext mcxt) +{ + Datum result; + + *isnull = false; + *typmod = -1; + + /* + * These types can holds JSON null, so must be processed + * before processing jbvNull. We don't want to convert + * JSON null, to SQL null, when targer is of JSON. + */ + if (expected_typid == JSONBOID || expected_typid == JSONOID) + { + Jsonb *jb = JsonbValueToJsonb(jbv); + + if (expected_typid == JSONOID) + { + char *str; + + str = JsonbToCString(NULL, &jb->root, VARSIZE(jb)); + result = PointerGetDatum(cstring_to_text(str)); + } + else + result = PointerGetDatum(jb); + + *typid = expected_typid; + } + + /* + * For special cases we can skip conversion to Jsonb + * and possibly IO cast. + */ + else if (jbv->type == jbvNull) + { + result = (Datum) 0; + *isnull = true; + *typid = expected_typid; + } + else if (jbv->type == jbvString) + { + text *txt = cstring_to_text_with_len(jbv->val.string.val, + jbv->val.string.len); + + result = PointerGetDatum(txt); + *typid = TEXTOID; + } + else if (jbv->type == jbvNumeric) + { + result = PointerGetDatum(jbv->val.numeric); + *typid = NUMERICOID; + } + else if (jbv->type == jbvBool) + { + result = BoolGetDatum(jbv->val.boolean); + *typid = BOOLOID; + } + + /* generic conversion */ + else + { + Jsonb *jb = JsonbValueToJsonb(jbv); + + result = json_populate_type(PointerGetDatum(jb), JSONBOID, + expected_typid, expected_typmod, + cache, mcxt, + isnull, false, NULL); + + *typid = expected_typid; + *typmod = expected_typmod; + } + + return result; +} + +static bool +jsonb_foreach_a_iterate(ForeachAIterator *self, + Datum *value, bool *isnull, + Oid *typid, int32 *typmod) +{ + JsonbForeachAIterator *iter = (JsonbForeachAIterator *) self; + JsonbIteratorToken r; + JsonbValue jbv; + + while ((r = JsonbIteratorNext(&iter->it, &jbv, iter->skip_nested)) != WJB_DONE) + { + iter->skip_nested = true; + + if (r == WJB_ELEM) + { + *value = JsonbValueToDatum(&jbv, typid, typmod, isnull, + iter->expected_typid, iter->expected_typmod, + &iter->cache, iter->cache_mcxt); + + return true; + } + } + + return false; +} + +/* + * Create foreach array iterator for jsonb array + */ +static ForeachAIterator * +create_jsonb_foreach_a_iterator(Datum value, Oid typid, int32 typmod, + int slice, Oid expected_typid, int32 expected_typmod) +{ + JsonbForeachAIterator *iter = palloc0(sizeof(JsonbForeachAIterator)); + Jsonb *jb; + + Assert(typid == JSONBOID); + + if (slice > 0) + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("jsonb array iterator doesn't support slicing"))); + + jb = DatumGetJsonbP(value); + + /* + * Jsonb iterator is designed like jsonb_array_element. Input value + * must be json array. + */ + if (JB_ROOT_IS_SCALAR(jb)) + ereport(ERROR, + (errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("cannot iterate over a scalar value."))); + else if (JB_ROOT_IS_OBJECT(jb)) + ereport(ERROR, + (errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("cannot iterate over an object value."))); + + Assert(JB_ROOT_IS_ARRAY(jb)); + + iter->it = JsonbIteratorInit(&jb->root); + + if (OidIsValid(expected_typid)) + { + iter->expected_typid = expected_typid; + iter->expected_typmod = expected_typmod; + } + else + { + iter->expected_typid = JSONBOID; + iter->expected_typmod = -1; + } + + iter->cache_mcxt = CurrentMemoryContext; + + iter->pub.iterate = jsonb_foreach_a_iterate; + + return (ForeachAIterator *) iter; +} + /* * jsonb_subscript_handler * Subscripting handler for jsonb. @@ -407,7 +600,8 @@ jsonb_subscript_handler(PG_FUNCTION_ARGS) .exec_setup = jsonb_exec_setup, .fetch_strict = true, /* fetch returns NULL for NULL inputs */ .fetch_leakproof = true, /* fetch returns NULL for bad subscript */ - .store_leakproof = false /* ... but assignment throws error */ + .store_leakproof = false, /* ... but assignment throws error */ + .create_foreach_a_iterator = create_jsonb_foreach_a_iterator }; PG_RETURN_POINTER(&sbsroutines); diff --git a/src/include/nodes/subscripting.h b/src/include/nodes/subscripting.h index 301f21dac2f..4dd223d1ef9 100644 --- a/src/include/nodes/subscripting.h +++ b/src/include/nodes/subscripting.h @@ -154,6 +154,40 @@ typedef void (*SubscriptExecSetup) (const SubscriptingRef *sbsref, SubscriptingRefState *sbsrefstate, SubscriptExecSteps *methods); +typedef struct _ForeachAIterator ForeachAIterator; + +/* + * ForeachAIiterator allows an iteration over a container object + * like an array of jsonb's array. Containers can holds a values of + * different types, so in any iteration, the returned value can be + * of any different type. Containers can holds composite types in own + * binary formats (jsonb object). We cannot to do safe conversion to + * Postgres's generic composite type without extra knowledge, + * so a user can specify the expected type (when it is known). + * Expected type can be unspecified (InvalidOid), but when + * it is specified, then it can be used for some other special cases + * like transformation of jsonb jbvNull to NULL or to string "null". + * Although the expected type is specified, the iterator doesn't enforce + * the conversion to this type (because conversion rules can be different + * in possible environments (SQL, PL/pgSQL). + */ +struct _ForeachAIterator +{ + bool (*iterate) (ForeachAIterator *self, + Datum *value, + bool *isnull, + Oid *typid, + int32 *typmod); + /* Private fields might appear beyond this point... */ +}; + +typedef ForeachAIterator * (*CreateForeachAIterator) (Datum value, + Oid typid, + int32 typmod, + int slice, + Oid expected_typid, + int32 expected_typmod); + /* Struct returned by the SQL-visible subscript handler function */ typedef struct SubscriptRoutines { @@ -163,6 +197,9 @@ typedef struct SubscriptRoutines bool fetch_leakproof; /* is fetch SubscriptingRef leakproof? */ bool store_leakproof; /* is assignment SubscriptingRef * leakproof? */ + + /* returns iterator used by PL/pgSQL FOREACH statement */ + CreateForeachAIterator create_foreach_a_iterator; } SubscriptRoutines; #endif /* SUBSCRIPTING_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..787d7698bd0 --- /dev/null +++ b/src/pl/plpgsql/src/expected/plpgsql_foreach.out @@ -0,0 +1,295 @@ +-- input must be a JSON array +do $$ +declare x numeric; +begin + foreach x in array NULL::jsonb -- 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 array +do $$ +declare x numeric; +begin + foreach x in array jsonb '10' -- fail + loop + raise notice '%', x; + end loop; +end; +$$; +ERROR: cannot iterate over a scalar value. +CONTEXT: PL/pgSQL function inline_code_block line 4 at FOREACH over array +do $$ +declare x numeric; +begin + foreach x in array jsonb '{}' -- fail + loop + raise notice '%', x; + end loop; +end; +$$; +ERROR: cannot iterate over an object value. +CONTEXT: PL/pgSQL function inline_code_block line 4 at FOREACH over array +-- numeric to numeric +do $$ +declare x numeric; +begin + foreach x in array jsonb '[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 array jsonb '[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 array jsonb '[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 array jsonb '[]' + 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 array jsonb '["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 array +do $$ +declare x boolean; +begin + foreach x in array jsonb '[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 array jsonb '[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 array jsonb '[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 array jsonb '[{}, {"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 array jsonb '[{}, {"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 array jsonb '[{}, {"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 array jsonb '[[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 array jsonb '[["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 array jsonb '[["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 array jsonb '[{"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 array jsonb '[{"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; +-- EXIT and CONTINUE can be triggered by LOOP_RC_PROCESSING +do $$ +declare x int; +begin + foreach x in array jsonb '[1,2,3,4,5]' + loop + exit when x = 3; + raise notice '%', x; + end loop; +end; +$$; +NOTICE: 1 +NOTICE: 2 +do $$ +declare x int; +begin + foreach x in array jsonb '[1,2,3,4,5]' + loop + continue when x % 2 = 0; + raise notice '%', x; + end loop; +end; +$$; +NOTICE: 1 +NOTICE: 3 +NOTICE: 5 +-- Variable instead of string +DO $$ +declare + x int; + arr jsonb; +begin + select jsonb_agg(i) into arr + from generate_series(1,3) g(i); + + foreach x in array arr + loop + raise notice '%', x; + end loop; +end; +$$; +NOTICE: 1 +NOTICE: 2 +NOTICE: 3 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 65b0fd0790f..16bb15c2207 100644 --- a/src/pl/plpgsql/src/pl_exec.c +++ b/src/pl/plpgsql/src/pl_exec.c @@ -29,6 +29,7 @@ #include "mb/stringinfo_mb.h" #include "miscadmin.h" #include "nodes/nodeFuncs.h" +#include "nodes/subscripting.h" #include "nodes/supportnodes.h" #include "optimizer/optimizer.h" #include "parser/parse_coerce.h" @@ -3026,40 +3027,51 @@ exec_stmt_forc(PLpgSQL_execstate *estate, PLpgSQL_stmt_forc *stmt) return rc; } - /* ---------- - * exec_stmt_foreach_a Loop over elements or slices of an array - * - * When looping over elements, the loop variable is the same type that the - * array stores (eg: integer), when looping through slices, the loop variable - * is an array of size and dimensions to match the size of the slice. - * ---------- + * exec_stmt_foreach_a Loop over elements in an array or jsonb array + * ---------- */ static int exec_stmt_foreach_a(PLpgSQL_execstate *estate, PLpgSQL_stmt_foreach_a *stmt) { - ArrayType *arr; - Oid arrtype; - int32 arrtypmod; - PLpgSQL_datum *loop_var; - Oid loop_var_elem_type; - bool found = false; + Datum expr; + Oid expr_typid; + int32 expr_typmod; + int16 expr_typlen; + bool expr_typbyval; + bool isnull; + PLpgSQL_datum *target_var; + Oid target_typid; + int32 target_typmod; + Oid target_collation; + Datum value; + Oid typid; + int32 typmod; int rc = PLPGSQL_RC_OK; + const struct SubscriptRoutines *sbroutines; + ForeachAIterator *iterator; MemoryContext stmt_mcontext; + MemoryContext tmp_cxt; MemoryContext oldcontext; - ArrayIterator array_iterator; - Oid iterator_result_type; - int32 iterator_result_typmod; - Datum value; - bool isnull; + bool found = false; - /* get the value of the array expression */ - value = exec_eval_expr(estate, stmt->expr, &isnull, &arrtype, &arrtypmod); + /* get the value of the expression */ + expr = exec_eval_expr(estate, stmt->expr, &isnull, + &expr_typid, &expr_typmod); if (isnull) ereport(ERROR, (errcode(ERRCODE_NULL_VALUE_NOT_ALLOWED), errmsg("FOREACH expression must not be null"))); + sbroutines = getSubscriptingRoutines(expr_typid, NULL); + if (!sbroutines) + ereport(ERROR, + (errcode(ERRCODE_DATATYPE_MISMATCH), + errmsg("cannot iterate over type %s because it does not support subscripting", + format_type_be(expr_typid)))); + + Assert(sbroutines->create_foreach_a_iterator); + /* * 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 @@ -3069,79 +3081,36 @@ exec_stmt_foreach_a(PLpgSQL_execstate *estate, PLpgSQL_stmt_foreach_a *stmt) push_stmt_mcontext(estate); oldcontext = MemoryContextSwitchTo(stmt_mcontext); - /* check the type of the expression - must be an array */ - if (!OidIsValid(get_element_type(arrtype))) - ereport(ERROR, - (errcode(ERRCODE_DATATYPE_MISMATCH), - errmsg("FOREACH expression must yield an array, not type %s", - format_type_be(arrtype)))); + /* Set up the target (loop) variable */ + target_var = estate->datums[stmt->varno]; + + plpgsql_exec_get_datum_type_info(estate, target_var, + &target_typid, &target_typmod, + &target_collation); /* * 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. */ - arr = DatumGetArrayTypePCopy(value); + get_typlenbyval(expr_typid, &expr_typlen, &expr_typbyval); + expr = datumCopy(expr, expr_typbyval, expr_typlen); + + iterator = sbroutines->create_foreach_a_iterator(expr, + expr_typid, expr_typmod, + stmt->slice, target_typid, + target_typmod); /* Clean up any leftover temporary memory */ exec_eval_cleanup(estate); - /* Slice dimension must be less than or equal to array dimension */ - if (stmt->slice < 0 || stmt->slice > ARR_NDIM(arr)) - ereport(ERROR, - (errcode(ERRCODE_ARRAY_SUBSCRIPT_ERROR), - errmsg("slice dimension (%d) is out of the valid range 0..%d", - stmt->slice, ARR_NDIM(arr)))); - - /* Set up the loop variable and see if it is of an array type */ - loop_var = estate->datums[stmt->varno]; - if (loop_var->dtype == PLPGSQL_DTYPE_REC || - loop_var->dtype == PLPGSQL_DTYPE_ROW) - { - /* - * Record/row variable is certainly not of array type, and might not - * be initialized at all yet, so don't try to get its type - */ - loop_var_elem_type = InvalidOid; - } - else - loop_var_elem_type = get_element_type(plpgsql_exec_get_datum_type(estate, - loop_var)); - - /* - * Sanity-check the loop variable type. We don't try very hard here, and - * should not be too picky since it's possible that exec_assign_value can - * coerce values of different types. But it seems worthwhile to complain - * if the array-ness of the loop variable is not right. - */ - if (stmt->slice > 0 && loop_var_elem_type == InvalidOid) - ereport(ERROR, - (errcode(ERRCODE_DATATYPE_MISMATCH), - errmsg("FOREACH ... SLICE loop variable must be of an array type"))); - if (stmt->slice == 0 && loop_var_elem_type != InvalidOid) - ereport(ERROR, - (errcode(ERRCODE_DATATYPE_MISMATCH), - errmsg("FOREACH loop variable must not be of an array type"))); - - /* Create an iterator to step through the array */ - array_iterator = array_create_iterator(arr, stmt->slice, NULL); + tmp_cxt = AllocSetContextCreate(stmt_mcontext, + "FOREACH IN ARRAY temporary cxt", + ALLOCSET_DEFAULT_SIZES); - /* Identify iterator result type */ - if (stmt->slice > 0) - { - /* When slicing, nominal type of result is same as array type */ - iterator_result_type = arrtype; - iterator_result_typmod = arrtypmod; - } - else - { - /* Without slicing, results are individual array elements */ - iterator_result_type = ARR_ELEMTYPE(arr); - iterator_result_typmod = arrtypmod; - } + MemoryContextSwitchTo(tmp_cxt); - /* Iterate over the array elements or slices */ - while (array_iterate(array_iterator, &value, &isnull)) + while (iterator->iterate(iterator, &value, &isnull, &typid, &typmod)) { found = true; /* looped at least once */ @@ -3149,12 +3118,9 @@ exec_stmt_foreach_a(PLpgSQL_execstate *estate, PLpgSQL_stmt_foreach_a *stmt) MemoryContextSwitchTo(oldcontext); /* Assign current element/slice to the loop variable */ - exec_assign_value(estate, loop_var, value, isnull, - iterator_result_type, iterator_result_typmod); + exec_assign_value(estate, target_var, value, isnull, typid, typmod); - /* In slice case, value is temporary; must free it to avoid leakage */ - if (stmt->slice > 0) - pfree(DatumGetPointer(value)); + MemoryContextReset(tmp_cxt); /* * Execute the statements @@ -3163,7 +3129,7 @@ exec_stmt_foreach_a(PLpgSQL_execstate *estate, PLpgSQL_stmt_foreach_a *stmt) LOOP_RC_PROCESSING(stmt->label, break); - MemoryContextSwitchTo(stmt_mcontext); + MemoryContextSwitchTo(tmp_cxt); } /* Restore memory context state */ @@ -5639,6 +5605,53 @@ 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); + } + + TupleDescFinalize(row->rowtupdesc); + + /* 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/plpgsql.h b/src/pl/plpgsql/src/plpgsql.h index addb14a9959..5cbdb4ecd9d 100644 --- a/src/pl/plpgsql/src/plpgsql.h +++ b/src/pl/plpgsql/src/plpgsql.h @@ -299,6 +299,7 @@ typedef struct PLpgSQL_datum { PLpgSQL_datum_type dtype; int dno; + char *refname; } PLpgSQL_datum; /* @@ -444,9 +445,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 */ 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..a64004417af --- /dev/null +++ b/src/pl/plpgsql/src/sql/plpgsql_foreach.sql @@ -0,0 +1,252 @@ +-- input must be a JSON array +do $$ +declare x numeric; +begin + foreach x in array NULL::jsonb -- fail + loop + raise notice '%', x; + end loop; +end; +$$; + +do $$ +declare x numeric; +begin + foreach x in array jsonb '10' -- fail + loop + raise notice '%', x; + end loop; +end; +$$; + +do $$ +declare x numeric; +begin + foreach x in array jsonb '{}' -- fail + loop + raise notice '%', x; + end loop; +end; +$$; + +-- numeric to numeric +do $$ +declare x numeric; +begin + foreach x in array jsonb '[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 array jsonb '[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 array jsonb '[10]' + loop + raise notice '%', x; + end loop; + raise notice 'FOUND: %', found; +end; +$$; + +-- test of FOUND variable +do $$ +declare x int; +begin + foreach x in array jsonb '[]' + 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 array jsonb '["10",20,30,"3.14"]' + loop + raise notice '%', x; + end loop; +end; +$$; + +do $$ +declare x boolean; +begin + foreach x in array jsonb '[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 array jsonb '[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 array jsonb '[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 array jsonb '[{}, {"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 array jsonb '[{}, {"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 array jsonb '[{}, {"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 array jsonb '[[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 array jsonb '[["Hi","Hello"],["Hello","Hi"]]' + loop + raise notice '% %', x[1], x[2]; + end loop; +end; +$$; + +do $$ +declare x varchar[]; +begin + foreach x in array jsonb '[["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 array jsonb '[{"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 array jsonb '[{"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; + +-- EXIT and CONTINUE can be triggered by LOOP_RC_PROCESSING +do $$ +declare x int; +begin + foreach x in array jsonb '[1,2,3,4,5]' + loop + exit when x = 3; + raise notice '%', x; + end loop; +end; +$$; + +do $$ +declare x int; +begin + foreach x in array jsonb '[1,2,3,4,5]' + loop + continue when x % 2 = 0; + raise notice '%', x; + end loop; +end; +$$; + +-- Variable instead of string +DO $$ +declare + x int; + arr jsonb; +begin + select jsonb_agg(i) into arr + from generate_series(1,3) g(i); + + foreach x in array arr + loop + raise notice '%', x; + end loop; +end; +$$; -- 2.54.0
