Re: POC: PLpgSQL FOREACH IN JSON ARRAY

> On Mar 5, 2026, at 02:50, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> wrote:
>
> Hi
>
> st 4. 3. 2026 v 12:35 odesílatel Jim Jones <jim(dot)jones(at)uni-muenster(dot)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/