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
