Re: proposal: plpgsql - FOREACH t IN JSON ARRAY expr

so 24. 1. 2026 v 8:10 odesílatel Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
napsal:

> Hi,
>
> last year I did a performance audit of some applications and I found a new
> relatively common pattern - iteration over jsonb arrays. Because PL/pgSQL
> doesn't support this iteration directly, they used some combinations of
> jsonb_array_elements function and FOR IN SELECT.
>
> The overhead of this can be relatively high, and I think we can support
> FOREACH json(b) arrays without some high cost.
>
> My idea is a support of syntax
>
> FOREACH target IN JSON ARRAY expression LOOP .. END LOOP
>

I did some simple test

CREATE OR REPLACE FUNCTION public.randoma(integer)
RETURNS integer[]
LANGUAGE sql
AS $function$
select array_agg(random()*10000) from generate_series(1,$1)
$function$

CREATE OR REPLACE FUNCTION public.suma(integer[])
RETURNS integer
LANGUAGE plpgsql
IMMUTABLE
AS $function$
declare s int default 0;
f int;
begin
foreach f in array $1 loop s := s + f; end loop;
return s;
end;
$function$

CREATE OR REPLACE FUNCTION public.sumj(jsonb)
RETURNS integer
LANGUAGE plpgsql
IMMUTABLE
AS $function$
declare s int default 0;
f int;
begin
for f in select v from jsonb_array_elements($1) g(v) loop s := s +
f::int; end loop;
return s;
end;
$function$

(2026-01-24 12:20:07) postgres=# do $$
declare a int[] = randoma(10000);
begin
for i in 1..1000 loop
perform suma(a);
end loop;
end;
$$;
DO
Time: 2705,881 ms (00:02,706)
(2026-01-24 12:20:11) postgres=# do $$
declare a jsonb = array_to_json(randoma(10000));
begin
for i in 1..1000 loop
perform sumj(a);
end loop;
end;
$$;
DO
Time: 25809,319 ms (00:25,809)

FOREACH is +/- 10 times faster

>
> target can be a scalar variable of any type - we can use cast rules used
> in JSON_TABLE
>
> What do you think about this proposal?
>
> Regards
>
> Pavel
>