Horton

Thread

  1. POC: PLpgSQL FOREACH IN JSON ARRAY

    Pavel Stehule <pavel.stehule@gmail.com> — 2026-02-28T07:10:53Z

    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

  2. Re: POC: PLpgSQL FOREACH IN JSON ARRAY

    Pavel Stehule <pavel.stehule@gmail.com> — 2026-03-01T05:44:17Z

    Hi

small update

Now assignment to plpgsql array variable and json array works. Some minor
code cleaning + more regress tests.

Regards

Pavel

  3. Re: POC: PLpgSQL FOREACH IN JSON ARRAY

    Pavel Stehule <pavel.stehule@gmail.com> — 2026-03-01T19:40:24Z

    ne 1. 3. 2026 v 6:44 odesílatel Pavel Stehule <pavel.stehule@gmail.com>
napsal:

> Hi
>
> small update
>
> Now assignment to plpgsql array variable and json array works. Some minor
> code cleaning + more regress tests.
>

fix uninitialized argument of  json_populate_type function

regards

Pavel


> Regards
>
> Pavel
>
>

  4. Re: POC: PLpgSQL FOREACH IN JSON ARRAY

    Jim Jones <jim.jones@uni-muenster.de> — 2026-03-03T07:42:59Z

    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


## 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"

## 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


## 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.")));


Thanks for the patch!

Best, Jim

  5. Re: POC: PLpgSQL FOREACH IN JSON ARRAY

    Pavel Stehule <pavel.stehule@gmail.com> — 2026-03-03T13:45:07Z

    Hi

út 3. 3. 2026 v 8:43 odesílatel Jim Jones <jim.jones@uni-muenster.de>
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
>

  6. Re: POC: PLpgSQL FOREACH IN JSON ARRAY

    Jim Jones <jim.jones@uni-muenster.de> — 2026-03-04T11:35:17Z

    I reviewed the code I have nothing to add at this point. LGTM!

The tests touch a lot of different scenarios, but for the sake of
completeness I'd like to suggest adding these three cases:

-- EXIT and CONTINUE can be triggered by LOOP_RC_PROCESSING
DO $$
DECLARE x int;
BEGIN
  FOREACH x IN JSON ARRAY '[1,2,3,4,5]'
  LOOP
    EXIT WHEN x = 3;
    RAISE NOTICE '%', x;
  END LOOP;
END;
$$;

DO $$
DECLARE x int;
BEGIN
  FOREACH x IN JSON ARRAY '[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) i;

  FOREACH x IN JSON ARRAY arr
  LOOP
    RAISE NOTICE '%', x;
  END LOOP;
END;
$$;


Thanks!

Best, Jim

  7. Re: POC: PLpgSQL FOREACH IN JSON ARRAY

    Pavel Stehule <pavel.stehule@gmail.com> — 2026-03-04T18:50:34Z

    Hi

st 4. 3. 2026 v 12:35 odesílatel Jim Jones <jim.jones@uni-muenster.de>
napsal:

> I reviewed the code I have nothing to add at this point. LGTM!
>
> The tests touch a lot of different scenarios, but for the sake of
> completeness I'd like to suggest adding these three cases:
>
> -- EXIT and CONTINUE can be triggered by LOOP_RC_PROCESSING
> DO $$
> DECLARE x int;
> BEGIN
>   FOREACH x IN JSON ARRAY '[1,2,3,4,5]'
>   LOOP
>     EXIT WHEN x = 3;
>     RAISE NOTICE '%', x;
>   END LOOP;
> END;
> $$;
>
> DO $$
> DECLARE x int;
> BEGIN
>   FOREACH x IN JSON ARRAY '[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) i;
>
>   FOREACH x IN JSON ARRAY arr
>   LOOP
>     RAISE NOTICE '%', x;
>   END LOOP;
> END;
> $$;
>
>
I merged these examples to tests

Thank you for review

Regards

Pavel


>
> Thanks!
>
> Best, Jim
>

  8. Re: POC: PLpgSQL FOREACH IN JSON ARRAY

    Shreeya Sharma <shreeya2304@gmail.com> — 2026-03-09T06:03:27Z

    Status: SUCCESS
Applied against: /Users/shreeyasharma/postgresql (Current HEAD)
Build status: PASS
Test status: PASS
Notes:
- Compilation passed: True
- Regression tests (make check) passed: True

  9. Re: POC: PLpgSQL FOREACH IN JSON ARRAY

    Shreeya Sharma <shreeya2304@gmail.com> — 2026-03-09T06:11:45Z

    The following review has been posted through the commitfest application:
make installcheck-world:  tested, passed
Implements feature:       tested, passed
Spec compliant:           tested, passed
Documentation:            tested, passed

The changes are good

The new status of this patch is: Ready for Committer

  10. Re: POC: PLpgSQL FOREACH IN JSON ARRAY

    Pavel Stehule <pavel.stehule@gmail.com> — 2026-03-09T06:44:03Z

    po 9. 3. 2026 v 7:12 odesílatel Shreeya Sharma <shreeya2304@gmail.com>
napsal:

> The following review has been posted through the commitfest application:
> make installcheck-world:  tested, passed
> Implements feature:       tested, passed
> Spec compliant:           tested, passed
> Documentation:            tested, passed
>
> The changes are good
>
> The new status of this patch is: Ready for Committer
>

Thank you very much

Regards

Pavel

  11. Re: POC: PLpgSQL FOREACH IN JSON ARRAY

    Peter Eisentraut <peter@eisentraut.org> — 2026-03-11T20:57:58Z

    On 28.02.26 08:10, Pavel Stehule wrote:
> I wrote PoC for previously proposed plpgsql statement FOREACH IN JSON ARRAY

Maybe this could be written in such a way that it doesn't hardcode JSON 
arrays specifically, but a type could have an iteration helper function 
that would feed this feature?

  12. Re: POC: PLpgSQL FOREACH IN JSON ARRAY

    Chao Li <li.evan.chao@gmail.com> — 2026-03-12T03:54:52Z

    
> On Mar 5, 2026, at 02:50, Pavel Stehule <pavel.stehule@gmail.com> wrote:
> 
> Hi
> 
> st 4. 3. 2026 v 12:35 odesílatel Jim Jones <jim.jones@uni-muenster.de> napsal:
> I reviewed the code I have nothing to add at this point. LGTM!
> 
> The tests touch a lot of different scenarios, but for the sake of
> completeness I'd like to suggest adding these three cases:
> 
> -- EXIT and CONTINUE can be triggered by LOOP_RC_PROCESSING
> DO $$
> DECLARE x int;
> BEGIN
>   FOREACH x IN JSON ARRAY '[1,2,3,4,5]'
>   LOOP
>     EXIT WHEN x = 3;
>     RAISE NOTICE '%', x;
>   END LOOP;
> END;
> $$;
> 
> DO $$
> DECLARE x int;
> BEGIN
>   FOREACH x IN JSON ARRAY '[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) i;
> 
>   FOREACH x IN JSON ARRAY arr
>   LOOP
>     RAISE NOTICE '%', x;
>   END LOOP;
> END;
> $$;
> 
> 
> I merged these examples to tests
> 
> Thank you for review
> 
> Regards
> 
> Pavel
>  
> Thanks!
> 
> Best, Jim
> <v20260304-5-0001-FOREACH-scalar-IN-JSON-ARRAY.patch>

I just reviewed and tested the patch. Here comes my comments:

1 - pl_gram.y
```
+								ereport(ERROR,
+										(errcode(ERRCODE_SYNTAX_ERROR),
+										 errmsg("not zero slice is allowed only for arrays"),
+												 parser_errposition(@4)));
```

* () around errcode and errmsg are no longer needed. This comment is general, and I saw other ereport() also use () in this patch.
* parser_errposition should have the same indention as errmsg.

2 - pl_exec.c
```
+				 errdetail("Cannot iterate over a object value.")));
```

Typo: a -> an

3 - pl_exec.c
```
+	tmp_cxt = AllocSetContextCreate(CurrentMemoryContext,
+									"FOREACH IN JSON ARRAY temporary cxt",
+									ALLOCSET_DEFAULT_SIZES);
```

Do we need to destroy tmp_cxt after the loop?

4 Looks like record type of loop var is not supported:
```
evantest=# do $$
declare
  r record;
begin
  foreach r in json array '[{"x":1,"y":"hi"},{"x":2,"y":"hello"}]’
  loop
    raise notice 'x: %, y: %', r.x, r.y; 
  end loop;
end;
$$;
ERROR:  record type has not been registered
CONTEXT:  PL/pgSQL function inline_code_block line 5 at FOREACH over json array
```

So, I want to check if you intentionally don’t want to support that or just missed that? If it’s not supported, then maybe document that.

5 I tried that composite type of loop var is supported, maybe add a test case for that. What I tested:
```
create type t_foreach_json_row as (
  x int,
  y text,
  z numeric
);

do $$
declare
  r t_foreach_json_row;
begin
  foreach r in json array
    '[{"x":1,"y":"one","z":1.5},
      {"x":2,"y":"two"},
      {"y":"three","z":3.14},
      {}]'
  loop
    raise notice 'x=%, y=%, z=%', r.x, r.y, r.z;
  end loop;
end;
$$;

drop type t_foreach_json_row;
```

Best regards,
--
Chao Li (Evan)
HighGo Software Co., Ltd.
https://www.highgo.com/

  13. Re: POC: PLpgSQL FOREACH IN JSON ARRAY

    Tom Lane <tgl@sss.pgh.pa.us> — 2026-03-12T04:30:11Z

    Peter Eisentraut <peter@eisentraut.org> writes:
> Maybe this could be written in such a way that it doesn't hardcode JSON 
> arrays specifically, but a type could have an iteration helper function 
> that would feed this feature?

+1.  ISTM that this feature would make sense for subscriptable types,
so one way to shoehorn it into the system without a lot of new overhead
could be to extend struct SubscriptRoutines to offer optional support
function(s) for iterating through all the elements of a subscriptable
object.

			regards, tom lane

  14. Re: POC: PLpgSQL FOREACH IN JSON ARRAY

    Pavel Stehule <pavel.stehule@gmail.com> — 2026-03-12T07:00:19Z

    Hi

čt 12. 3. 2026 v 5:30 odesílatel Tom Lane <tgl@sss.pgh.pa.us> napsal:

> Peter Eisentraut <peter@eisentraut.org> writes:
> > Maybe this could be written in such a way that it doesn't hardcode JSON
> > arrays specifically, but a type could have an iteration helper function
> > that would feed this feature?
>
> +1.  ISTM that this feature would make sense for subscriptable types,
> so one way to shoehorn it into the system without a lot of new overhead
> could be to extend struct SubscriptRoutines to offer optional support
> function(s) for iterating through all the elements of a subscriptable
> object.
>

I'll try to write second patch in this way

Regards

Pavel


>
>                         regards, tom lane
>

  15. Re: POC: PLpgSQL FOREACH IN JSON ARRAY

    Pavel Stehule <pavel.stehule@gmail.com> — 2026-03-12T10:38:11Z

    Hi

čt 12. 3. 2026 v 4:55 odesílatel Chao Li <li.evan.chao@gmail.com> napsal:

>
>
> > On Mar 5, 2026, at 02:50, Pavel Stehule <pavel.stehule@gmail.com> wrote:
> >
> > Hi
> >
> > st 4. 3. 2026 v 12:35 odesílatel Jim Jones <jim.jones@uni-muenster.de>
> napsal:
> > I reviewed the code I have nothing to add at this point. LGTM!
> >
> > The tests touch a lot of different scenarios, but for the sake of
> > completeness I'd like to suggest adding these three cases:
> >
> > -- EXIT and CONTINUE can be triggered by LOOP_RC_PROCESSING
> > DO $$
> > DECLARE x int;
> > BEGIN
> >   FOREACH x IN JSON ARRAY '[1,2,3,4,5]'
> >   LOOP
> >     EXIT WHEN x = 3;
> >     RAISE NOTICE '%', x;
> >   END LOOP;
> > END;
> > $$;
> >
> > DO $$
> > DECLARE x int;
> > BEGIN
> >   FOREACH x IN JSON ARRAY '[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) i;
> >
> >   FOREACH x IN JSON ARRAY arr
> >   LOOP
> >     RAISE NOTICE '%', x;
> >   END LOOP;
> > END;
> > $$;
> >
> >
> > I merged these examples to tests
> >
> > Thank you for review
> >
> > Regards
> >
> > Pavel
> >
> > Thanks!
> >
> > Best, Jim
> > <v20260304-5-0001-FOREACH-scalar-IN-JSON-ARRAY.patch>
>
> I just reviewed and tested the patch. Here comes my comments:
>
> 1 - pl_gram.y
> ```
> +
>  ereport(ERROR,
> +
>      (errcode(ERRCODE_SYNTAX_ERROR),
> +
>       errmsg("not zero slice is allowed only for arrays"),
> +
>                       parser_errposition(@4)));
> ```
>
> * () around errcode and errmsg are no longer needed. This comment is
> general, and I saw other ereport() also use () in this patch.
> * parser_errposition should have the same indention as errmsg.
>

fixed


>
> 2 - pl_exec.c
> ```
> +                                errdetail("Cannot iterate over a object
> value.")));
> ```
>
> Typo: a -> an
>

fixed


>
> 3 - pl_exec.c
> ```
> +       tmp_cxt = AllocSetContextCreate(CurrentMemoryContext,
> +
>  "FOREACH IN JSON ARRAY temporary cxt",
> +
>  ALLOCSET_DEFAULT_SIZES);
> ```
>
> Do we need to destroy tmp_cxt after the loop?
>

no - it is cleaned by MemoryContextReset(stmt_mcontext);

>
> 4 Looks like record type of loop var is not supported:
> ```
> evantest=# do $$
> declare
>   r record;
> begin
>   foreach r in json array '[{"x":1,"y":"hi"},{"x":2,"y":"hello"}]’
>   loop
>     raise notice 'x: %, y: %', r.x, r.y;
>   end loop;
> end;
> $$;
> ERROR:  record type has not been registered
> CONTEXT:  PL/pgSQL function inline_code_block line 5 at FOREACH over json
> array
> ```
>
> So, I want to check if you intentionally don’t want to support that or
> just missed that? If it’s not supported, then maybe document that.
>

It is intentional at this moment (and I think so it will be in future too).
For reading fields from a json object I use the json_populate_type
function, and this function needs a known tupdesc. Generally JSON objects
have no fixed structure, and when a record's variable has no assigned type,
then we have to create new tupdesc for each value. This can be possibly
slow and memory expensive. Probably - I never tested this case. It is valid
use case, but it can be solved in later - and the support will be more
invasive - requires support in json_populate_type

I enhanced doc

    <para>
     The target variable can be of type RECORD, but the real structure has
to be
     assigned before usage in FOREACH statement.
    </para>




>
> 5 I tried that composite type of loop var is supported, maybe add a test
> case for that. What I tested:
> ```
> create type t_foreach_json_row as (
>   x int,
>   y text,
>   z numeric
> );
>
> do $$
> declare
>   r t_foreach_json_row;
> begin
>   foreach r in json array
>     '[{"x":1,"y":"one","z":1.5},
>       {"x":2,"y":"two"},
>       {"y":"three","z":3.14},
>       {}]'
>   loop
>     raise notice 'x=%, y=%, z=%', r.x, r.y, r.z;
>   end loop;
> end;
> $$;
>
> drop type t_foreach_json_row;
> ```
>

it is there already

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;
$$;

assigned updated version -

I'll try to modify this patch like Tom proposed in the next version. But
the fundament behavior should be same

Thank you for check and testing

Regards

Pavel




>
> Best regards,
> --
> Chao Li (Evan)
> HighGo Software Co., Ltd.
> https://www.highgo.com/
>
>
>
>
>

  16. Re: POC: PLpgSQL FOREACH IN JSON ARRAY

    Pavel Stehule <pavel.stehule@gmail.com> — 2026-03-17T06:58:20Z

    Hi

čt 12. 3. 2026 v 8:00 odesílatel Pavel Stehule <pavel.stehule@gmail.com>
napsal:

> Hi
>
> čt 12. 3. 2026 v 5:30 odesílatel Tom Lane <tgl@sss.pgh.pa.us> napsal:
>
>> Peter Eisentraut <peter@eisentraut.org> writes:
>> > Maybe this could be written in such a way that it doesn't hardcode JSON
>> > arrays specifically, but a type could have an iteration helper function
>> > that would feed this feature?
>>
>> +1.  ISTM that this feature would make sense for subscriptable types,
>> so one way to shoehorn it into the system without a lot of new overhead
>> could be to extend struct SubscriptRoutines to offer optional support
>> function(s) for iterating through all the elements of a subscriptable
>> object.
>>
>
>
attached patch do this - new interface has two
methods: CreateForeachAIterator and iterate

diff --git a/src/include/nodes/subscripting.h
b/src/include/nodes/subscripting.h
index 301f21dac2f..08bfe59ede4 100644
--- a/src/include/nodes/subscripting.h
+++ b/src/include/nodes/subscripting.h
@@ -154,6 +154,32 @@ typedef void (*SubscriptExecSetup) (const
SubscriptingRef *sbsref,
                                    SubscriptingRefState *sbsrefstate,
                                    SubscriptExecSteps *methods);

+typedef struct _ForeachAIterator ForeachAIterator;
+
+/*
+ * ForeachAIiterator is used by PLpgSQL FOREACH IN ARRAY statement.
+ * Input value should not be null, and inside CreateForeachAIterator
+ * routine must be copied to current (statement) context. "iterate"
+ * routine is called under short life memory context, that is resetted
+ * after any call.
+ */
+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 target_typid,
+                                                     int32 target_typmod);
+
 /* Struct returned by the SQL-visible subscript handler function */
 typedef struct SubscriptRoutines
 {
@@ -163,6 +189,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 */

Regards

Pavel


>
>

> Regards
>
> Pavel
>
>
>>
>>                         regards, tom lane
>>
>

  17. Re: POC: PLpgSQL FOREACH IN JSON ARRAY

    Pavel Stehule <pavel.stehule@gmail.com> — 2026-03-21T18:40:06Z

    Hi

only rebase

Regards

Pavel

  18. Re: POC: PLpgSQL FOREACH IN JSON ARRAY

    Pavel Stehule <pavel.stehule@gmail.com> — 2026-05-26T06:51:41Z

    Hi

fresh rebase

Regards

Pavel

  19. Re: POC: PLpgSQL FOREACH IN JSON ARRAY

    Pavel Stehule <pavel.stehule@gmail.com> — 2026-06-16T07:52:59Z

    Hi

rebase

Regards

Pavel

  20. Re: POC: PLpgSQL FOREACH IN JSON ARRAY

    Tom Lane <tgl@sss.pgh.pa.us> — 2026-07-01T21:40:04Z

    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.

* 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.

* memory management may need to be rethought a bit as well.
Certainly the comments referencing exec_eval_cleanup do not
belong here.

			regards, tom lane

  21. Re: POC: PLpgSQL FOREACH IN JSON ARRAY

    Pavel Stehule <pavel.stehule@gmail.com> — 2026-07-05T15:36:18Z

    Hi

st 1. 7. 2026 v 23:40 odesílatel Tom Lane <tgl@sss.pgh.pa.us> 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.
>

I changed the interface like you proposed. It reduces some code, but
introduces some new issues:

1. Because the iterator has no knowledge about target type, the json object
values cannot be assigned to a list of target values.
Our composites and JSON objects have not compatible IO format. With
knowledge of target type, I can force transformation from
jsonb to composite. There is not any generic API that can help with this,
and it can be implemented as part of the iterator. And in
this case IO cast can be a problem (if we support it) because jsonb doesn't
preserve an order of fields.

2. When I know the target is jsonb I don't need to immediately transform
jbvNull to NULL.

3. If I don't do any transformation inside the iterator and returns just an
iterated value, then I have a problem with often usage of IO cast - that is
more strict.

when I iterate over numeric array and target is int, then I got cast
int4(numeric) - "3.14 --> 3"

when I iterate over jsonb array and target is int, then I got IO cast -
there is int4(jsonb) cast, but this is explicit and then cast "3.14" to int
fails.
Maybe we can enhance plpgsql cast functions to see inside jsonb and then
can better to choose cast function.

This inconsistency is simply visible:

(2026-07-05 17:31:13) postgres=# do $$ declare t int; begin t :=
'3.14'::numeric; end $$;
DO
(2026-07-05 17:31:22) postgres=# do $$ declare t int; begin t :=
'3.14'::jsonb; end $$;
ERROR:  invalid input syntax for type integer: "3.14"
CONTEXT:  PL/pgSQL assignment "t := '3.14'::jsonb"
PL/pgSQL function inline_code_block line 1 at assignment
(2026-07-05 17:31:34) postgres=# do $$ declare t int; begin t :=
'3.14'::jsonb::int; end $$;
DO



>
> * 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 think the error messages can be written more generically.


> * memory management may need to be rethought a bit as well.
> Certainly the comments referencing exec_eval_cleanup do not
> belong here.
>

I changed it,

Regards

Pavel


>
>                         regards, tom lane
>

  22. Re: POC: PLpgSQL FOREACH IN JSON ARRAY

    Pavel Stehule <pavel.stehule@gmail.com> — 2026-07-07T20:03:47Z

    Hi

st 1. 7. 2026 v 23:40 odesílatel Tom Lane <tgl@sss.pgh.pa.us> 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
>

  23. Re: POC: PLpgSQL FOREACH IN JSON ARRAY

    Pavel Stehule <pavel.stehule@gmail.com> — 2026-07-08T12:44:38Z

    Hi

fix regress tests

Regards

Pavel