public inbox for [email protected]  

help / color / mirror / Atom feed

From: Mark Dilger <[email protected]>
To: Alexandra Wang <[email protected]>
Cc: Matheus Alcantara <[email protected]>
Cc: Peter Eisentraut <[email protected]>
Cc: Andrew Dunstan <[email protected]>
Cc: Nikita Glukhov <[email protected]>
Cc: PostgreSQL Hackers <[email protected]>
Cc: David E. Wheeler <[email protected]>
Cc: jian he <[email protected]>
Subject: Re: SQL:2023 JSON simplified accessor support
Date: Tue, 4 Mar 2025 06:05:18 -0800
Message-ID: <CAHgHdKtL9nNaKXGCLt9gWugVzYWKhoBDQ7NESUwdCBty8kFK-A@mail.gmail.com> (raw)
In-Reply-To: <CAK98qZ14uKKRVFg4ibzMfReYaZD6Byxq8nYnvNNtXNjCfcd8kQ@mail.gmail.com>
References: <[email protected]>
	<[email protected]>
	<CAK98qZ2QGcyJrJAFv9wjY6S8yP9dUVnmG9Gb4OXuzuMMuM1Z5Q@mail.gmail.com>
	<[email protected]>
	<[email protected]>
	<CAK98qZ3Ly6PhRwCVmMKJBba5oHVF9k370MMT2b_gep-SuQfRtg@mail.gmail.com>
	<CAK98qZ1za8XgOLY+2hQMPGoxVYFyh=dDkM2dZPVeJK4J6poyvA@mail.gmail.com>
	<CAK98qZ0EfrPcv3ZwGdeDiLPEpXYfHOEc8S2D=OCPJGcyWy5d-g@mail.gmail.com>
	<CAK98qZ1rZaVNy6ViangQom4iinZVH7=ebqhTsPxMQbN5ZtE9XQ@mail.gmail.com>
	<CAFY6G8cY5SUG5L-0ryVpom6HynE49p8-XQ59qkaEgnZZJ-c4Rg@mail.gmail.com>
	<CAFY6G8eUGcU3A4AHprgYbSOAOj7+WGhGxS_YP0wd2+aCpZTiNg@mail.gmail.com>
	<CAK98qZ14uKKRVFg4ibzMfReYaZD6Byxq8nYnvNNtXNjCfcd8kQ@mail.gmail.com>

On Mon, Mar 3, 2025 at 12:23 PM Alexandra Wang <[email protected]>
wrote:

>  I've attached v10, which addresses your feedback.
>
>
Hi Alex!  Thanks for the patches.

In src/test/regress/sql/jsonb.sql, the section marked with "-- slices are
not supported" should be relabeled.  That comment predates these patches,
and is now misleading.

A bit further down in expected/jsonb.out, there is an expected failure, but
no SQL comment to indicate that it is expected:

+SELECT (t.jb).* FROM test_jsonb_dot_notation;
+ERROR:  missing FROM-clause entry for table "t"
+LINE 1: SELECT (t.jb).* FROM test_jsonb_dot_notation;

Perhaps a "-- fails" comment would clarify?  Then, further down,

+SELECT (jb).a.**.x FROM test_jsonb_dot_notation; -- not supported
+ERROR:  syntax error at or near "**"
+LINE 1: SELECT (jb).a.**.x FROM test_jsonb_dot_notation;

I wonder if it would be better to have the parser handle this case and
raise a ERRCODE_FEATURE_NOT_SUPPORTED instead?

I got curious about the support for this new dot notation in the plpgsql
parser and tried:

+DO $$
+DECLARE
+  a jsonb := '[1,2,3,4,5,6,7]'::jsonb;
+BEGIN
+  WHILE a IS NOT NULL
+  LOOP
+    RAISE NOTICE '%', a;
+    a := a[2:];
+  END LOOP;
+END
+$$ LANGUAGE plpgsql;
+NOTICE:  [1, 2, 3, 4, 5, 6, 7]
+NOTICE:  [3, 4, 5, 6, 7]
+NOTICE:  [5, 6, 7]
+NOTICE:  7

which looks good!  But then I tried:

+DO $$
+DECLARE
+  a jsonb := '{"": 6, "NU": [{"": [[3]]}, [6], [2], "bCi"], "aaf": [-6,
-8]}'::jsonb;
+BEGIN
+  WHILE a IS NOT NULL
+  LOOP
+    RAISE NOTICE '%', a;
+    a := 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 := COALESCE(a."NU", a[2])
+                      ^
+QUERY:  a := 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 notice there are no changes in src/interfaces/ecpg/test, which concerns
me.  The sqljson.pgc and sqljson_jsontable.pgc files are already testing
json handling in ecpg; perhaps just extend those a bit?

—
Mark Dilger
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

view thread (67+ messages)  latest in thread

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], [email protected], [email protected], [email protected], [email protected], [email protected]
  Subject: Re: SQL:2023 JSON simplified accessor support
  In-Reply-To: <CAHgHdKtL9nNaKXGCLt9gWugVzYWKhoBDQ7NESUwdCBty8kFK-A@mail.gmail.com>

* 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