Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tpUIa-006MZp-DU for pgsql-hackers@arkaria.postgresql.org; Tue, 04 Mar 2025 15:35:12 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1tpUIZ-000YyU-4E for pgsql-hackers@arkaria.postgresql.org; Tue, 04 Mar 2025 15:35:11 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tpUIY-000YvW-Nc for pgsql-hackers@lists.postgresql.org; Tue, 04 Mar 2025 15:35:10 +0000 Received: from mail-oa1-x2b.google.com ([2001:4860:4864:20::2b]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1tpUIT-000vJ6-3C for pgsql-hackers@postgresql.org; Tue, 04 Mar 2025 15:35:09 +0000 Received: by mail-oa1-x2b.google.com with SMTP id 586e51a60fabf-2bcca6aae0bso3929864fac.1 for ; Tue, 04 Mar 2025 07:35:06 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=enterprisedb.com; s=google; t=1741102505; x=1741707305; darn=postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=ucXVbwdbI0/u0pk2wOOnNhNUWKj9fjTj2ovyL/XZCHc=; b=XLIj2RhIeD0G7EQXRYGhxcTnX8H5VsgFik+Q6SAiLZ5jgN+/qonbTu7la3+Wz2jr5N jCm3niGZ+eeWBthtOwvYYaLRu7U6ba0U9T/VplRdxnXTA/FkMFXMNwPtjRzP0KDy77u3 QH6nyhMS8IoHq526ncGMQ9s+U5KDw/Z/7E+IACxS44owDPEiMElPDjiawem9yuZrZM8Q XLyUahYTP1Hk6ge9pYulo3zIzdjL12XqqchczTLUdYggmyExy32xIb/02eCcVKerLNGq oXd2hfKZfXzcM/rNinKRERLIGGQVLUWQNjhiEQme3pN7NP637NIbM3zqQ1bLrl7AWSQr /85g== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1741102505; x=1741707305; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=ucXVbwdbI0/u0pk2wOOnNhNUWKj9fjTj2ovyL/XZCHc=; b=ZjZJcwqSNWescc4S45dMBAgC+47/e167LO7t2xsaYECD/leJROXEZIHC+iyyIIHztW Wbv3yEYkgYD6XoEIyNgNeQizLpvj9vxq/R/VEqVr/N61RVK5tgEvEX/HVKSBSWHfTjOU mE2EqItDTCeBrAvhdrgbgGnAS8gNUfvabgwtnr63Q/6O9m3MXRKMo+r4IFNulKPQhPba yHpQL8jnYzQK4KTdbTjoqGdGil7oTwhsN1ZKUfx8RTlZEjs9jyWO/uCr5tBREyGmm5RB aVwYY0STR8UJbpk9X1iBcsH+uE3YAod0dB5Qz/hj3GXiH2yy5EdJSQtMt9zWTCnOq29e mvyg== X-Forwarded-Encrypted: i=1; AJvYcCVLce0rLnJ/jLUV09b2RbAPHFiA3svk4MOXlX2EqPvB+LrPG0iHQX85o1XeKniHSLEmzyepGfhKBWYYlxph@postgresql.org X-Gm-Message-State: AOJu0YyUNu2dSh1dadxwiVVNirovRytT8h99TCylf86FcF2b4c5Qh25b pKUrxFDBMU2VEh/gPuaXI2i8GyDiM3PvQG3Ux5K/DHRobX9fp+kPjodSz8PuBYdK59/8wXEoU7w 4L/hk67kDDWSHM2/PwvbZ+7u0vl118hzQNnT0 X-Gm-Gg: ASbGncs2V7uGllwWf9rKuTB3UsEDcKM1jzF4o/7G8qdRTuc72Sf9r+4V7zjA74NZ0p3 ub1z6M2LMA5TceS8nLZzJOvgDAbMr9cj2kx5nK8EsSILAsJsgys7noR0CHIXeo+VhIne4P8fcAD TK4kno+OMnuL9ZN4ZMyE8jjnU2Tw== X-Google-Smtp-Source: AGHT+IHOt/y3MmR3s9KaPExuMuncloGmwUYy/9VHFuV2OWEdo+xUC6LACdqHrlbNcHR6/2yIP6XWfMrYkP8OON7HZIQ= X-Received: by 2002:a05:6871:a6ac:b0:2b7:f2dc:a4bf with SMTP id 586e51a60fabf-2c178431816mr9309215fac.18.1741102504212; Tue, 04 Mar 2025 07:35:04 -0800 (PST) MIME-Version: 1.0 References: <21fa36b4-6d56-4556-b524-9e3955b922c8@eisentraut.org> In-Reply-To: From: Mark Dilger Date: Tue, 4 Mar 2025 07:34:53 -0800 X-Gm-Features: AQ5f1Jr0iIH7NIeztcZhM7M7ktSpbRlxNhhDDbxx487E2S1rzLWBVBGofZnEm7o Message-ID: Subject: Re: SQL:2023 JSON simplified accessor support To: Alexandra Wang Cc: Matheus Alcantara , Peter Eisentraut , Andrew Dunstan , Nikita Glukhov , PostgreSQL Hackers , "David E. Wheeler" , jian he Content-Type: multipart/alternative; boundary="000000000000481e01062f8604ef" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000481e01062f8604ef Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable On Tue, Mar 4, 2025 at 6:05=E2=80=AFAM Mark Dilger wrote: > But then I tried: > > +DO $$ > +DECLARE > + a jsonb :=3D '{"": 6, "NU": [{"": [[3]]}, [6], [2], "bCi"], "aaf": [-6= , > -8]}'::jsonb; > +BEGIN > + WHILE a IS NOT NULL > + LOOP > + RAISE NOTICE '%', a; > + a :=3D COALESCE(a."NU", a[2]); > + END LOOP; > +END > +$$ LANGUAGE plpgsql; > +NOTICE: {"": 6, "NU": [{"": [[3]]}, [6], [2], "bCi"], "aaf": [-6, -8]} > +ERROR: missing FROM-clause entry for table "a" > +LINE 1: a :=3D COALESCE(a."NU", a[2]) > + ^ > +QUERY: a :=3D COALESCE(a."NU", a[2]) > +CONTEXT: PL/pgSQL function inline_code_block line 8 at assignment > > which suggests the plpgsql parser does not recognize a."NU" as we'd > expect. Any thoughts on this? > I should mention that +DO $$ +DECLARE + a jsonb :=3D '{"": 6, "NU": [{"": [[3]]}, [6], [2], "bCi"], "aaf": [-6, -8]}'::jsonb; +BEGIN + WHILE a IS NOT NULL + LOOP + RAISE NOTICE '%', a; + a :=3D COALESCE((a)."NU", (a)[2]); + END LOOP; +END +$$ LANGUAGE plpgsql; +NOTICE: {"": 6, "NU": [{"": [[3]]}, [6], [2], "bCi"], "aaf": [-6, -8]} +NOTICE: [{"": [[3]]}, [6], [2], "bCi"] +NOTICE: [2] works fine. I guess that is good enough. Should we add these to the sql/jsonb.sql to document the expected behavior, both with the error when using plain "a" and with the correct output when using "(a)"? The reason I mention this is that the plpgsql parser might get changed at some point, and without a test case, we might not notice if this breaks. =E2=80=94 Mark Dilger EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company --000000000000481e01062f8604ef Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable


On Tue, Mar 4, = 2025 at 6:05=E2=80=AFAM Mark Dilger <mark.dilger@enterprisedb.com> wrote:
But then I tried:
+DO $$
+DECLARE
+ =C2=A0a jsonb :=3D '{"":= 6, "NU": [{"": [[3]]}, [6], [2], "bCi"], &qu= ot;aaf": [-6, -8]}'::jsonb;
+BEGIN
+ =C2=A0WHILE a IS NOT NU= LL
+ =C2=A0LOOP
+ =C2=A0 =C2=A0RAISE NOTICE '%', a;
+ =C2= =A0 =C2=A0a :=3D COALESCE(a."NU", a[2]);
+ =C2=A0END LOOP;
= +END
+$$ LANGUAGE plpgsql;
+NOTICE: =C2=A0{"": 6, "NU&= quot;: [{"": [[3]]}, [6], [2], "bCi"], "aaf":= [-6, -8]}
+ERROR: =C2=A0missing FROM-clause entry for table "a&quo= t;
+LINE 1: a :=3D COALESCE(a."NU", a[2])
+ =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0^
+QUERY: = =C2=A0a :=3D COALESCE(a."NU", a[2])
+CONTEXT: =C2=A0PL/pgSQL f= unction inline_code_block line 8 at assignment

which suggests the plpgsql parser does not recognize a."NU" as w= e'd expect.=C2=A0 Any thoughts on this?

I should mention that

+DO= $$
+DECLARE
+ =C2=A0a jsonb :=3D '{"": 6, "NU&q= uot;: [{"": [[3]]}, [6], [2], "bCi"], "aaf": = [-6, -8]}'::jsonb;
+BEGIN
+ =C2=A0WHILE a IS NOT NULL
+ =C2=A0= LOOP
+ =C2=A0 =C2=A0RAISE NOTICE '%', a;
+ =C2=A0 =C2=A0a := =3D COALESCE((a)."NU", (a)[2]);
+ =C2=A0END LOOP;
+END
+= $$ LANGUAGE plpgsql;
+NOTICE: =C2=A0{"": 6, "NU": [{= "": [[3]]}, [6], [2], "bCi"], "aaf": [-6, -8]= }
+NOTICE: =C2=A0[{"": [[3]]}, [6], [2], "bCi"]
+= NOTICE: =C2=A0[2]
=C2=A0
works fine.=C2=A0 I guess that i= s good enough.=C2=A0 Should we add these to the sql/jsonb.sql to document t= he expected behavior, both with the error when using plain "a" an= d with the correct output when using "(a)"?=C2=A0 The reason I me= ntion this is that the plpgsql parser might get changed at some point, and = without a test case, we might not notice if this breaks.

=E2=80=94
Mark Dil= ger
En= terpriseDB:=C2=A0http://= www.enterprisedb.com
The Enterprise PostgreSQL Company
--000000000000481e01062f8604ef--