v49-0001-validate-parsing-back-json_table.no-cfbot
application/octet-stream
Filename: v49-0001-validate-parsing-back-json_table.no-cfbot
Type: application/octet-stream
Part: 0
Message:
Re: remaining sql/json patches
From ba310fcb06b291a3611bcd84a415d60817bfe880 Mon Sep 17 00:00:00 2001
From: jian he <jian.universality@gmail.com>
Date: Thu, 4 Apr 2024 14:34:58 +0800
Subject: [PATCH v49 1/1] validate parsing back json_table
json_table syntax is quite long.
human eyes are not easy to found out the parsing back json_table
is the correct. we need a way to validate parse back json_table works. we can
do it through view. main gottcha is query: select count(*) from ((select *
from view except all literal_view_def)) should return zero.
---
.../regress/expected/sqljson_jsontable.out | 87 +++++++++++++++----
src/test/regress/sql/sqljson_jsontable.sql | 40 ++++++++-
2 files changed, 109 insertions(+), 18 deletions(-)
diff --git a/src/test/regress/expected/sqljson_jsontable.out b/src/test/regress/expected/sqljson_jsontable.out
index 77c04d97..ac69e920 100644
--- a/src/test/regress/expected/sqljson_jsontable.out
+++ b/src/test/regress/expected/sqljson_jsontable.out
@@ -218,6 +218,37 @@ FROM json_table_test vals
(14 rows)
-- JSON_TABLE: Test backward parsing
+drop function if exists validate_view_def;
+NOTICE: function validate_view_def() does not exist, skipping
+CREATE OR REPLACE FUNCTION validate_view_def(view_name text) RETURNS bigint AS
+$func$
+DECLARE
+ cnt bigint := 0;
+ temp text;
+ view_def text;
+ query text;
+ r record;
+ r1 record;
+BEGIN
+ temp := $sql$
+ SELECT pg_catalog.pg_get_viewdef(c.oid, true)
+ FROM pg_catalog.pg_class c
+ LEFT JOIN pg_catalog.pg_namespace n ON c.relnamespace = n.oid
+ where c.relkind = 'v' and c.relname = $sql$ || quote_literal($1);
+ --step1, get the pg_get_viewdef.
+ EXECUTE format('%s ', temp) into r;
+ ---step2 replace the last semicolon in pg_get_viewdef.
+ if (right(r.pg_get_viewdef,1) = ';') then
+ view_def := substr(r.pg_get_viewdef, 1, length(r.pg_get_viewdef) - 1);
+ end if;
+ -- step3, execute the viewdef string.
+ EXECUTE format('%s ', r.pg_get_viewdef) into r1;
+ --step4, compare viewdef with the view.
+ query := 'select count(*) from (( ' || view_def || ' ) except all ( select * from ' || quote_ident($1) || '))';
+ -- raise notice '%', query;
+ EXECUTE format('%s ', query) into cnt;
+ return cnt;
+END $func$ LANGUAGE plpgsql;
CREATE VIEW jsonb_table_view2 AS
SELECT * FROM
JSON_TABLE(
@@ -234,7 +265,6 @@ SELECT * FROM
JSON_TABLE(
jsonb 'null', 'lax $[*]' PASSING 1 + 2 AS a, json '"foo"' AS "b c"
COLUMNS (
- js json PATH '$',
jb jsonb PATH '$',
jst text FORMAT JSON PATH '$',
jsc char(4) FORMAT JSON PATH '$',
@@ -261,7 +291,6 @@ SELECT * FROM
JSON_TABLE(
jsonb 'null', 'lax $[*]' PASSING 1 + 2 AS a, json '"foo"' AS "b c"
COLUMNS (
- js2 json PATH '$',
jsb2w jsonb PATH '$' WITH WRAPPER,
jsb2q jsonb PATH '$' OMIT QUOTES,
ia int[] PATH '$',
@@ -291,8 +320,7 @@ CREATE OR REPLACE VIEW public.jsonb_table_view2 AS
)
\sv jsonb_table_view3
CREATE OR REPLACE VIEW public.jsonb_table_view3 AS
- SELECT js,
- jb,
+ SELECT jb,
jst,
jsc,
jsv
@@ -302,7 +330,6 @@ CREATE OR REPLACE VIEW public.jsonb_table_view3 AS
1 + 2 AS a,
'"foo"'::json AS "b c"
COLUMNS (
- js json PATH '$',
jb jsonb PATH '$',
jst text FORMAT JSON PATH '$',
jsc character(4) FORMAT JSON PATH '$',
@@ -345,8 +372,7 @@ CREATE OR REPLACE VIEW public.jsonb_table_view5 AS
)
\sv jsonb_table_view6
CREATE OR REPLACE VIEW public.jsonb_table_view6 AS
- SELECT js2,
- jsb2w,
+ SELECT jsb2w,
jsb2q,
ia,
ta,
@@ -357,7 +383,6 @@ CREATE OR REPLACE VIEW public.jsonb_table_view6 AS
1 + 2 AS a,
'"foo"'::json AS "b c"
COLUMNS (
- js2 json PATH '$',
jsb2w jsonb PATH '$' WITH UNCONDITIONAL WRAPPER,
jsb2q jsonb PATH '$' OMIT QUOTES,
ia integer[] PATH '$',
@@ -365,6 +390,36 @@ CREATE OR REPLACE VIEW public.jsonb_table_view6 AS
jba jsonb[] PATH '$'
)
)
+SELECT validate_view_def('jsonb_table_view2');
+ validate_view_def
+-------------------
+ 0
+(1 row)
+
+SELECT validate_view_def('jsonb_table_view3');
+ validate_view_def
+-------------------
+ 0
+(1 row)
+
+SELECT validate_view_def('jsonb_table_view4');
+ validate_view_def
+-------------------
+ 0
+(1 row)
+
+SELECT validate_view_def('jsonb_table_view5');
+ validate_view_def
+-------------------
+ 0
+(1 row)
+
+SELECT validate_view_def('jsonb_table_view6');
+ validate_view_def
+-------------------
+ 0
+(1 row)
+
EXPLAIN (COSTS OFF, VERBOSE) SELECT * FROM jsonb_table_view2;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
@@ -374,11 +429,11 @@ EXPLAIN (COSTS OFF, VERBOSE) SELECT * FROM jsonb_table_view2;
(3 rows)
EXPLAIN (COSTS OFF, VERBOSE) SELECT * FROM jsonb_table_view3;
- QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ QUERY PLAN
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Table Function Scan on "json_table"
- Output: "json_table".js, "json_table".jb, "json_table".jst, "json_table".jsc, "json_table".jsv
- Table Function Call: JSON_TABLE('null'::jsonb, '$[*]' AS json_table_path_0 PASSING 3 AS a, '"foo"'::jsonb AS "b c" COLUMNS (js json PATH '$', jb jsonb PATH '$', jst text FORMAT JSON PATH '$', jsc character(4) FORMAT JSON PATH '$', jsv character varying(4) FORMAT JSON PATH '$'))
+ Output: "json_table".jb, "json_table".jst, "json_table".jsc, "json_table".jsv
+ Table Function Call: JSON_TABLE('null'::jsonb, '$[*]' AS json_table_path_0 PASSING 3 AS a, '"foo"'::jsonb AS "b c" COLUMNS (jb jsonb PATH '$', jst text FORMAT JSON PATH '$', jsc character(4) FORMAT JSON PATH '$', jsv character varying(4) FORMAT JSON PATH '$'))
(3 rows)
EXPLAIN (COSTS OFF, VERBOSE) SELECT * FROM jsonb_table_view4;
@@ -398,11 +453,11 @@ EXPLAIN (COSTS OFF, VERBOSE) SELECT * FROM jsonb_table_view5;
(3 rows)
EXPLAIN (COSTS OFF, VERBOSE) SELECT * FROM jsonb_table_view6;
- QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ QUERY PLAN
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Table Function Scan on "json_table"
- Output: "json_table".js2, "json_table".jsb2w, "json_table".jsb2q, "json_table".ia, "json_table".ta, "json_table".jba
- Table Function Call: JSON_TABLE('null'::jsonb, '$[*]' AS json_table_path_0 PASSING 3 AS a, '"foo"'::jsonb AS "b c" COLUMNS (js2 json PATH '$', jsb2w jsonb PATH '$' WITH UNCONDITIONAL WRAPPER, jsb2q jsonb PATH '$' OMIT QUOTES, ia integer[] PATH '$', ta text[] PATH '$', jba jsonb[] PATH '$'))
+ Output: "json_table".jsb2w, "json_table".jsb2q, "json_table".ia, "json_table".ta, "json_table".jba
+ Table Function Call: JSON_TABLE('null'::jsonb, '$[*]' AS json_table_path_0 PASSING 3 AS a, '"foo"'::jsonb AS "b c" COLUMNS (jsb2w jsonb PATH '$' WITH UNCONDITIONAL WRAPPER, jsb2q jsonb PATH '$' OMIT QUOTES, ia integer[] PATH '$', ta text[] PATH '$', jba jsonb[] PATH '$'))
(3 rows)
-- JSON_TABLE() with alias
diff --git a/src/test/regress/sql/sqljson_jsontable.sql b/src/test/regress/sql/sqljson_jsontable.sql
index bdce4636..7b80b1ad 100644
--- a/src/test/regress/sql/sqljson_jsontable.sql
+++ b/src/test/regress/sql/sqljson_jsontable.sql
@@ -118,6 +118,38 @@ FROM json_table_test vals
-- JSON_TABLE: Test backward parsing
+drop function if exists validate_view_def;
+CREATE OR REPLACE FUNCTION validate_view_def(view_name text) RETURNS bigint AS
+$func$
+DECLARE
+ cnt bigint := 0;
+ temp text;
+ view_def text;
+ query text;
+ r record;
+ r1 record;
+BEGIN
+ temp := $sql$
+ SELECT pg_catalog.pg_get_viewdef(c.oid, true)
+ FROM pg_catalog.pg_class c
+ LEFT JOIN pg_catalog.pg_namespace n ON c.relnamespace = n.oid
+ where c.relkind = 'v' and c.relname = $sql$ || quote_literal($1);
+ --step1, get the pg_get_viewdef.
+ EXECUTE format('%s ', temp) into r;
+ ---step2 replace the last semicolon in pg_get_viewdef.
+ if (right(r.pg_get_viewdef,1) = ';') then
+ view_def := substr(r.pg_get_viewdef, 1, length(r.pg_get_viewdef) - 1);
+ end if;
+ -- step3, execute the viewdef string.
+ EXECUTE format('%s ', r.pg_get_viewdef) into r1;
+ --step4, compare viewdef with the view.
+ query := 'select count(*) from (( ' || view_def || ' ) except all ( select * from ' || quote_ident($1) || '))';
+ -- raise notice '%', query;
+ EXECUTE format('%s ', query) into cnt;
+ return cnt;
+END $func$ LANGUAGE plpgsql;
+
+
CREATE VIEW jsonb_table_view2 AS
SELECT * FROM
JSON_TABLE(
@@ -135,7 +167,6 @@ SELECT * FROM
JSON_TABLE(
jsonb 'null', 'lax $[*]' PASSING 1 + 2 AS a, json '"foo"' AS "b c"
COLUMNS (
- js json PATH '$',
jb jsonb PATH '$',
jst text FORMAT JSON PATH '$',
jsc char(4) FORMAT JSON PATH '$',
@@ -165,7 +196,6 @@ SELECT * FROM
JSON_TABLE(
jsonb 'null', 'lax $[*]' PASSING 1 + 2 AS a, json '"foo"' AS "b c"
COLUMNS (
- js2 json PATH '$',
jsb2w jsonb PATH '$' WITH WRAPPER,
jsb2q jsonb PATH '$' OMIT QUOTES,
ia int[] PATH '$',
@@ -178,6 +208,12 @@ SELECT * FROM
\sv jsonb_table_view5
\sv jsonb_table_view6
+SELECT validate_view_def('jsonb_table_view2');
+SELECT validate_view_def('jsonb_table_view3');
+SELECT validate_view_def('jsonb_table_view4');
+SELECT validate_view_def('jsonb_table_view5');
+SELECT validate_view_def('jsonb_table_view6');
+
EXPLAIN (COSTS OFF, VERBOSE) SELECT * FROM jsonb_table_view2;
EXPLAIN (COSTS OFF, VERBOSE) SELECT * FROM jsonb_table_view3;
EXPLAIN (COSTS OFF, VERBOSE) SELECT * FROM jsonb_table_view4;
base-commit: 2a217c371799ae3ecd8d32a137cea874fad7f5dc
prerequisite-patch-id: 19eaa6cd330842b7147245e46120a3962de325bc
--
2.34.1