Re: POC: PLpgSQL FOREACH IN JSON ARRAY

Hi

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

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

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