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: