proposal: plpgsql - FOREACH t IN JSON ARRAY expr

so 24. 1. 2026 v 8:10 odesílatel Pavel Stehule <pavel.stehule@gmail.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