Re: SQL:2023 JSON simplified accessor support

On 2025-03-04 Tu 10:34 AM, Mark Dilger wrote:
>
>
> On Tue, Mar 4, 2025 at 6:05 AM Mark Dilger
> <mark(dot)dilger(at)enterprisedb(dot)com> wrote:
>
> 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 should mention that
>
> +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]}
> +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.
>

Yes, I think so.

cheers

andrew

--
Andrew Dunstan
EDB:https://www.enterprisedb.com