Re: remaining sql/json patches - Mailing list pgsql-hackers

From jian he
Subject Re: remaining sql/json patches
Date
Msg-id CACJufxEuoSbR=4DUx7q2br=VZvTEye1iBnZtj8LinhDn8N1uJA@mail.gmail.com
Whole thread Raw
In response to Re: remaining sql/json patches  (Amit Langote <amitlangote09@gmail.com>)
Responses Re: remaining sql/json patches
List pgsql-hackers
On Wed, Apr 3, 2024 at 8:39 PM Amit Langote <amitlangote09@gmail.com> wrote:
>
> Attached updated patches.  I have addressed your doc comments on 0001,
> but not 0002 yet.
>

in v49, 0002.
+\sv jsonb_table_view1
+CREATE OR REPLACE VIEW public.jsonb_table_view1 AS
+ SELECT id,
+    a1,
+    b1,
+    a11,
+    a21,
+    a22
+   FROM JSON_TABLE(
+            'null'::jsonb, '$[*]' AS json_table_path_0
+            PASSING
+                1 + 2 AS a,
+                '"foo"'::json AS "b c"
+            COLUMNS (
+                id FOR ORDINALITY,
+                a1 integer PATH '$."a1"',
+                b1 text PATH '$."b1"',
+                a11 text PATH '$."a11"',
+                a21 text PATH '$."a21"',
+                a22 text PATH '$."a22"',
+                NESTED PATH '$[1]' AS p1
+                COLUMNS (
+                    id FOR ORDINALITY,
+                    a1 integer PATH '$."a1"',
+                    b1 text PATH '$."b1"',
+                    a11 text PATH '$."a11"',
+                    a21 text PATH '$."a21"',
+                    a22 text PATH '$."a22"',
+                    NESTED PATH '$[*]' AS "p1 1"
+                    COLUMNS (
+                        id FOR ORDINALITY,
+                        a1 integer PATH '$."a1"',
+                        b1 text PATH '$."b1"',
+                        a11 text PATH '$."a11"',
+                        a21 text PATH '$."a21"',
+                        a22 text PATH '$."a22"'
+                    )
+                ),
+                NESTED PATH '$[2]' AS p2
+                COLUMNS (
+                    id FOR ORDINALITY,
+                    a1 integer PATH '$."a1"',
+                    b1 text PATH '$."b1"',
+                    a11 text PATH '$."a11"',
+                    a21 text PATH '$."a21"',
+                    a22 text PATH '$."a22"'
+                    NESTED PATH '$[*]' AS "p2:1"
+                    COLUMNS (
+                        id FOR ORDINALITY,
+                        a1 integer PATH '$."a1"',
+                        b1 text PATH '$."b1"',
+                        a11 text PATH '$."a11"',
+                        a21 text PATH '$."a21"',
+                        a22 text PATH '$."a22"'
+                    ),
+                    NESTED PATH '$[*]' AS p22
+                    COLUMNS (
+                        id FOR ORDINALITY,
+                        a1 integer PATH '$."a1"',
+                        b1 text PATH '$."b1"',
+                        a11 text PATH '$."a11"',
+                        a21 text PATH '$."a21"',
+                        a22 text PATH '$."a22"'
+                    )
+                )
+            )
+        )

execute this view definition (not the "create view") will have syntax error.
That means the changes in v49,0002 ruleutils.c are wrong.
also \sv the output is quite long, not easy to validate it.

we need a way to validate that the view definition is equivalent to
"select * from view".
so I added a view validate function to it.

we can put it in v49, 0001.
since json data type don't equality operator,
so I did some minor change to make the view validate  function works with
jsonb_table_view2
jsonb_table_view3
jsonb_table_view4
jsonb_table_view5
jsonb_table_view6

Attachment

pgsql-hackers by date:

Previous
From: Thomas Munro
Date:
Subject: [MASSMAIL]WIP: Vectored writeback
Next
From: Laurenz Albe
Date:
Subject: Re: postgres_fdw fails because GMT != UTC