On 2025-03-04 Tu 10:34 AM, Mark Dilger wrote:
On Tue, Mar 4, 2025 at 6:05 AM Mark Dilger <[email protected]> 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
