POC: PLpgSQL FOREACH IN JSON ARRAY

Hi

small update

Now assignment to plpgsql array variable and json array works. Some minor
code cleaning + more regress tests.

Regards

Pavel

ne 1. 3. 2026 v 6:44 odesílatel Pavel Stehule <pavel.stehule@gmail.com>
napsal:

Hi

small update

Now assignment to plpgsql array variable and json array works. Some minor
code cleaning + more regress tests.

fix uninitialized argument of json_populate_type function

regards

Pavel

Hi Pavel,

I quickly tested the patch, and I also could observe a ~3x performance
improvement!

A few first impressions:

## in exec_stmt_foreach_json_a the boolean variable found is declared as
false, bit its value is never set to true until exec_set_found() is called:

/*
* Set the FOUND variable to indicate the result of executing the loop
* (namely, whether we looped one or more times). This must be set here so
* that it does not interfere with the value of the FOUND variable inside
* the loop processing itself.
*/
exec_set_found(estate, found);

Test:

DO $$
DECLARE
x int;
BEGIN
FOREACH x IN JSON ARRAY '[1,2,3]'
LOOP
RAISE NOTICE 'x: %', x;
END LOOP;

IF FOUND THEN
RAISE NOTICE 'FOUND is true';
ELSE
RAISE NOTICE 'FOUND is false';
END IF;
END;
$$;
NOTICE: x: 1
NOTICE: x: 2
NOTICE: x: 3
NOTICE: FOUND is false

## Suggestion in the plpgsql.sgml

The <literal>FOREACH</literal> loop is much like a
<literal>FOREACH</literal> loop,

to

"much like a regular <literal>FOREACH</literal> loop over arrays"

## Typo in comment

/*
* We cannot to use fieldnames for tupdescentry, because
* these names can be suffixed by name of row variable.
...

We cannot to use > We cannot use

## Nit pick

These error messages are not wrong, but IMO a errhint/errdetail could
add some value here:

ereport(ERROR,
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
errmsg("cannot extract elements from a scalar")));

ereport(ERROR,
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
errmsg("cannot extract elements from an object")));

Something like this perhaps?

ereport(ERROR,
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
errmsg("cannot extract elements from a scalar"),
errhint("FOREACH IN JSON ARRAY requires an array value.")));

ereport(ERROR,
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
errmsg("FOREACH expression must evaluate to a JSON array"),
errdetail("Cannot iterate over a scalar value.")));

Thanks for the patch!

Best, Jim

Hi

út 3. 3. 2026 v 8:43 odesílatel Jim Jones <jim.jones@uni-muenster.de>
napsal:

Hi Pavel,

I quickly tested the patch, and I also could observe a ~3x performance
improvement!

A few first impressions:

## in exec_stmt_foreach_json_a the boolean variable found is declared as
false, bit its value is never set to true until exec_set_found() is called:

/*
* Set the FOUND variable to indicate the result of executing the loop
* (namely, whether we looped one or more times). This must be set here so
* that it does not interfere with the value of the FOUND variable inside
* the loop processing itself.
*/
exec_set_found(estate, found);

Test:

DO $$
DECLARE
x int;
BEGIN
FOREACH x IN JSON ARRAY '[1,2,3]'
LOOP
RAISE NOTICE 'x: %', x;
END LOOP;

IF FOUND THEN
RAISE NOTICE 'FOUND is true';
ELSE
RAISE NOTICE 'FOUND is false';
END IF;
END;
$$;
NOTICE: x: 1
NOTICE: x: 2
NOTICE: x: 3
NOTICE: FOUND is false

fixed + regress tests

## Suggestion in the plpgsql.sgml

The <literal>FOREACH</literal> loop is much like a
<literal>FOREACH</literal> loop,

to

"much like a regular <literal>FOREACH</literal> loop over arrays"

done

## Typo in comment

/*
* We cannot to use fieldnames for tupdescentry, because
* these names can be suffixed by name of row variable.
...

We cannot to use > We cannot use

fixed

## Nit pick

These error messages are not wrong, but IMO a errhint/errdetail could
add some value here:

ereport(ERROR,
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
errmsg("cannot extract elements from a scalar")));

ereport(ERROR,
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
errmsg("cannot extract elements from an object")));

Something like this perhaps?

ereport(ERROR,
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
errmsg("cannot extract elements from a scalar"),
errhint("FOREACH IN JSON ARRAY requires an array value.")));

ereport(ERROR,
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
errmsg("FOREACH expression must evaluate to a JSON array"),
errdetail("Cannot iterate over a scalar value.")));

I rewrote it to

if (JB_ROOT_IS_SCALAR(jb))
ereport(ERROR,
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
errmsg("FOREACH expression must evaluate to a JSON array"),
errhint("Cannot iterate over a scalar value.")));
else if (JB_ROOT_IS_OBJECT(jb))
ereport(ERROR,
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
errmsg("FOREACH expression must evaluate to a JSON array"),
errdetail("Cannot iterate over a object value.")));

Assert(JB_ROOT_IS_ARRAY(jb));

+ regress tests

Thanks for the patch!

Thank you for check

Regards

Pavel

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;
$$;

Thanks!

Best, Jim

Hi

st 4. 3. 2026 v 12:35 odesílatel Jim Jones <jim.jones@uni-muenster.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

Status: SUCCESS
Applied against: /Users/shreeyasharma/postgresql (Current HEAD)
Build status: PASS
Test status: PASS
Notes:
- Compilation passed: True
- Regression tests (make check) passed: True

The following review has been posted through the commitfest application:
make installcheck-world: tested, passed
Implements feature: tested, passed
Spec compliant: tested, passed
Documentation: tested, passed

The changes are good

The new status of this patch is: Ready for Committer

po 9. 3. 2026 v 7:12 odesílatel Shreeya Sharma <shreeya2304@gmail.com>
napsal:

The following review has been posted through the commitfest application:
make installcheck-world: tested, passed
Implements feature: tested, passed
Spec compliant: tested, passed
Documentation: tested, passed

The changes are good

The new status of this patch is: Ready for Committer

Thank you very much

Regards

Pavel