Content-Type: text/plain;
	charset=utf-8
Mime-Version: 1.0 (Mac OS X Mail 16.0 \(3864.400.21\))
Subject: Re: POC: PLpgSQL FOREACH IN JSON ARRAY
From: Chao Li <li.evan.chao@gmail.com>
In-Reply-To: 
 <CAFj8pRA-GKmYig4A23mUyCxEquYPK_t2acg0s_KHWhadh_w5MA@mail.gmail.com>
Date: Thu, 12 Mar 2026 11:54:52 +0800
Cc: Jim Jones <jim.jones@uni-muenster.de>,
 PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Content-Transfer-Encoding: quoted-printable
Message-Id: <EC293375-3C09-4A6F-9B76-E5FF8A9E7165@gmail.com>
References: 
 <CAFj8pRD9Jjv+m=6DP6vWWn5NnZeTPH9eoPFCnA1JE5hRKRDMxQ@mail.gmail.com>
 <CAFj8pRCBUtakcgBF2uZ-+2rq9Kzw-p0V1i6B73gfiVjwKct3AQ@mail.gmail.com>
 <CAFj8pRA7TLOTuY-Dry2SaSpaPaqUJpsJke8Bm2SyZaVh2p5y4Q@mail.gmail.com>
 <d6ac54b9-79f4-485b-a540-96c56cd0b991@uni-muenster.de>
 <CAFj8pRDi-7S7cWgtsvdHm8vvhvd8xHQ7tdXymBfBQQgpYangPQ@mail.gmail.com>
 <f408910c-e19f-42b0-ae6a-e8bceecc3bc2@uni-muenster.de>
 <CAFj8pRA-GKmYig4A23mUyCxEquYPK_t2acg0s_KHWhadh_w5MA@mail.gmail.com>
To: Pavel Stehule <pavel.stehule@gmail.com>
Archived-At: 
 <https://www.postgresql.org/message-id/EC293375-3C09-4A6F-9B76-E5FF8A9E7165%40gmail.com>
Precedence: bulk


> On Mar 5, 2026, at 02:50, Pavel Stehule <pavel.stehule@gmail.com> =
wrote:
>=20
> Hi
>=20
> st 4. 3. 2026 v 12:35 odes=C3=ADlatel Jim Jones =
<jim.jones@uni-muenster.de> napsal:
> I reviewed the code I have nothing to add at this point. LGTM!
>=20
> The tests touch a lot of different scenarios, but for the sake of
> completeness I'd like to suggest adding these three cases:
>=20
> -- 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 =3D 3;
>     RAISE NOTICE '%', x;
>   END LOOP;
> END;
> $$;
>=20
> DO $$
> DECLARE x int;
> BEGIN
>   FOREACH x IN JSON ARRAY '[1,2,3,4,5]'
>   LOOP
>     CONTINUE WHEN x % 2 =3D 0;
>     RAISE NOTICE '%', x;
>   END LOOP;
> END;
> $$;
>=20
>=20
> -- Variable instead of string
> DO $$
> DECLARE x int; arr jsonb;
> BEGIN
>   SELECT jsonb_agg(i) INTO arr
>   FROM generate_series(1,3) i;
>=20
>   FOREACH x IN JSON ARRAY arr
>   LOOP
>     RAISE NOTICE '%', x;
>   END LOOP;
> END;
> $$;
>=20
>=20
> I merged these examples to tests
>=20
> Thank you for review
>=20
> Regards
>=20
> Pavel
> =20
> Thanks!
>=20
> Best, Jim
> <v20260304-5-0001-FOREACH-scalar-IN-JSON-ARRAY.patch>

I just reviewed and tested the patch. Here comes my comments:

1 - pl_gram.y
```
+								=
ereport(ERROR,
+										=
(errcode(ERRCODE_SYNTAX_ERROR),
+										=
 errmsg("not zero slice is allowed only for arrays"),
+										=
		 parser_errposition(@4)));
```

* () around errcode and errmsg are no longer needed. This comment is =
general, and I saw other ereport() also use () in this patch.
* parser_errposition should have the same indention as errmsg.

2 - pl_exec.c
```
+				 errdetail("Cannot iterate over a object =
value.")));
```

Typo: a -> an

3 - pl_exec.c
```
+	tmp_cxt =3D AllocSetContextCreate(CurrentMemoryContext,
+									=
"FOREACH IN JSON ARRAY temporary cxt",
+									=
ALLOCSET_DEFAULT_SIZES);
```

Do we need to destroy tmp_cxt after the loop?

4 Looks like record type of loop var is not supported:
```
evantest=3D# do $$
declare
  r record;
begin
  foreach r in json array '[{"x":1,"y":"hi"},{"x":2,"y":"hello"}]=E2=80=99=

  loop
    raise notice 'x: %, y: %', r.x, r.y;=20
  end loop;
end;
$$;
ERROR:  record type has not been registered
CONTEXT:  PL/pgSQL function inline_code_block line 5 at FOREACH over =
json array
```

So, I want to check if you intentionally don=E2=80=99t want to support =
that or just missed that? If it=E2=80=99s not supported, then maybe =
document that.

5 I tried that composite type of loop var is supported, maybe add a test =
case for that. What I tested:
```
create type t_foreach_json_row as (
  x int,
  y text,
  z numeric
);

do $$
declare
  r t_foreach_json_row;
begin
  foreach r in json array
    '[{"x":1,"y":"one","z":1.5},
      {"x":2,"y":"two"},
      {"y":"three","z":3.14},
      {}]'
  loop
    raise notice 'x=3D%, y=3D%, z=3D%', r.x, r.y, r.z;
  end loop;
end;
$$;

drop type t_foreach_json_row;
```

Best regards,
--
Chao Li (Evan)
HighGo Software Co., Ltd.
https://www.highgo.com/