POC: PLpgSQL FOREACH IN JSON ARRAY

Hi

I wrote PoC for previously proposed plpgsql statement FOREACH IN JSON ARRAY

It looks like:

do $$
declare x int;
begin
foreach x in json array '[1,2,3,4]'
loop
raise notice 'x: %', x;
end loop;
end;
$$

do $$
declare x int; y int;
begin
foreach x, y in json array '[{"x": 100, "y": 1000}, {"y": 1000, "x":
100}]'
loop
raise notice 'x: %, y: %', x, y;
end loop;
end
$$

My first motivation for this patch is performance. This is faster (3 - 4 x)
than using FOR IN SELECT FROM json_array_elements, because there is no
overhead of SQL executor. Second motivation is a little bit better
readability, because inside plpgsql' statements we have info about used
variables and we can use it.

The behavior is very similar to FOREACH IN ARRAY with one significant
difference - the values of JSON objects are assigned to the composite
variable or lists of variables by names (not by position). It made this
decision because jsonb doesn't preserve the position of the field in
object, and then assignment based on position cannot work.

The code is relatively short now - about 400 lines +/- and the code is
simple without risks.

There are some open questions - mainly if default mode for mapping json
fields to plpgsql variables should be in lax or strict mode. Now, it is
something between (cast errors are raised) - it is consistent
with jsonb_populate_record - but it should not be the final design. I
cannot say what is better - currently implemented behavior is consistent
with common plpgsql behaviour, but SQL/JSON is different. I can imagine
that default behaviour will be lax, and with some optional clauses we can
push behave to strict mode. I have no strong opinion about it. Maybe I
prefer the current "strict" behaviour a little bit, because it is more
"safe", but it is only my personal opinion. But again, I have no strong
opinion about this question and I very much invite any discussion about it.

This is proof of concept patch - casting between plpgsql arrays and json
arrays is not supported, documentation and regress tests are minimalistic,
but it is good enough for testing and good enough for decision, if this
feature is wanted or not (or if it needs some modifications).

This is a new feature (and proprietary feature). There should not be any
compatibility issues.

What do you think about this feature?

Regards

Pavel