Обсуждение: POC: PLpgSQL FOREACH IN JSON ARRAY

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

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

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

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

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?

> 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
thispatch. 
* 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
maybedocument 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/

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