Re: remaining sql/json patches

On Wed, Apr 3, 2024 at 8:39 PM Amit Langote <amitlangote09(at)gmail(dot)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