public inbox for [email protected]  

help / color / mirror / Atom feed

From: Jim Jones <[email protected]>
To: Pavel Stehule <[email protected]>
To: PostgreSQL Hackers <[email protected]>
Subject: Re: POC: PLpgSQL FOREACH IN JSON ARRAY
Date: Tue, 3 Mar 2026 08:42:59 +0100
Message-ID: <[email protected]> (raw)
In-Reply-To: <CAFj8pRA7TLOTuY-Dry2SaSpaPaqUJpsJke8Bm2SyZaVh2p5y4Q@mail.gmail.com>
References: <CAFj8pRD9Jjv+m=6DP6vWWn5NnZeTPH9eoPFCnA1JE5hRKRDMxQ@mail.gmail.com>
	<CAFj8pRCBUtakcgBF2uZ-+2rq9Kzw-p0V1i6B73gfiVjwKct3AQ@mail.gmail.com>
	<CAFj8pRA7TLOTuY-Dry2SaSpaPaqUJpsJke8Bm2SyZaVh2p5y4Q@mail.gmail.com>

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

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected]
  Subject: Re: POC: PLpgSQL FOREACH IN JSON ARRAY
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox