Re: POC: PLpgSQL FOREACH IN JSON ARRAY

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


>
> Best, Jim
>