From 72beebd39471390aacc119885e2bfa270a995115 Mon Sep 17 00:00:00 2001 From: Amit Langote Date: Wed, 22 Nov 2023 13:18:58 +0900 Subject: [PATCH v26 4/5] Remove RETURNING clause from JSON_EXISTS --- doc/src/sgml/func.sgml | 1 - src/backend/parser/gram.y | 5 +- src/test/regress/expected/jsonb_sqljson.out | 64 +-------------------- src/test/regress/sql/jsonb_sqljson.sql | 18 ------ 4 files changed, 3 insertions(+), 85 deletions(-) diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml index c6965ea343..a4b0e0636a 100644 --- a/doc/src/sgml/func.sgml +++ b/doc/src/sgml/func.sgml @@ -18213,7 +18213,6 @@ $.* ? (@ like_regex "^\\d+$") json_exists json_exists ( context_item, path_expression PASSING { value AS varname } , ... - RETURNING data_type { TRUE | FALSE | UNKNOWN | ERROR } ON ERROR ) diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y index 4cdf086a42..5d7e089f4c 100644 --- a/src/backend/parser/gram.y +++ b/src/backend/parser/gram.y @@ -15785,7 +15785,6 @@ func_expr_common_subexpr: } | JSON_EXISTS '(' json_value_expr ',' a_expr json_passing_clause_opt - json_returning_clause_opt json_behavior_clause_opt ')' { @@ -15795,8 +15794,8 @@ func_expr_common_subexpr: n->context_item = (JsonValueExpr *) $3; n->pathspec = $5; n->passing = $6; - n->output = (JsonOutput *) $7; - n->behavior = $8; + n->output = NULL; + n->behavior = $7; n->location = @1; $$ = (Node *) n; } diff --git a/src/test/regress/expected/jsonb_sqljson.out b/src/test/regress/expected/jsonb_sqljson.out index 9bf3fb21db..8b835817f0 100644 --- a/src/test/regress/expected/jsonb_sqljson.out +++ b/src/test/regress/expected/jsonb_sqljson.out @@ -140,64 +140,6 @@ SELECT JSON_EXISTS(jsonb '1', '$.a > 2' ERROR ON ERROR); t (1 row) --- extension: RETURNING clause -SELECT JSON_EXISTS(jsonb '1', '$[0]' RETURNING bool); - json_exists -------------- - t -(1 row) - -SELECT JSON_EXISTS(jsonb '1', '$[1]' RETURNING bool); - json_exists -------------- - f -(1 row) - -SELECT JSON_EXISTS(jsonb '1', '$[0]' RETURNING int); - json_exists -------------- - 1 -(1 row) - -SELECT JSON_EXISTS(jsonb '1', '$[1]' RETURNING int); - json_exists -------------- - 0 -(1 row) - -SELECT JSON_EXISTS(jsonb '1', '$[0]' RETURNING text); - json_exists -------------- - true -(1 row) - -SELECT JSON_EXISTS(jsonb '1', '$[1]' RETURNING text); - json_exists -------------- - false -(1 row) - -SELECT JSON_EXISTS(jsonb '1', 'strict $[1]' RETURNING text FALSE ON ERROR); - json_exists -------------- - false -(1 row) - -SELECT JSON_EXISTS(jsonb '1', '$[0]' RETURNING jsonb); -ERROR: cannot cast type boolean to jsonb -LINE 1: SELECT JSON_EXISTS(jsonb '1', '$[0]' RETURNING jsonb); - ^ -SELECT JSON_EXISTS(jsonb '1', '$[0]' RETURNING float4); -ERROR: cannot cast type boolean to real -LINE 1: SELECT JSON_EXISTS(jsonb '1', '$[0]' RETURNING float4); - ^ -SELECT JSON_EXISTS(jsonb '1', '$[0]' RETURNING float4 FORMAT JSON); -- RETURNING FORMAT not allowed -ERROR: cannot specify FORMAT in RETURNING clause of JSON_EXISTS() -LINE 1: ...CT JSON_EXISTS(jsonb '1', '$[0]' RETURNING float4 FORMAT JSO... - ^ --- RETUGNING pseudo-types not allowed -SELECT JSON_EXISTS(jsonb '1', '$[0]' RETURNING record); -ERROR: returning pseudo-types is not supported in SQL/JSON functions -- JSON_VALUE SELECT JSON_VALUE(NULL::jsonb, '$'); json_value @@ -998,8 +940,6 @@ CREATE TABLE test_jsonb_constraints ( CHECK (JSON_QUERY(js::jsonb, '$.a' WITH CONDITIONAL WRAPPER EMPTY OBJECT ON ERROR) < jsonb '[10]') CONSTRAINT test_jsonb_constraint5 CHECK (JSON_QUERY(js::jsonb, '$.a' RETURNING char(5) OMIT QUOTES EMPTY ARRAY ON EMPTY) > 'a' COLLATE "C") - CONSTRAINT test_jsonb_constraint6 - CHECK (JSON_EXISTS(js::jsonb, 'strict $.a' RETURNING int TRUE ON ERROR) < 2) ); \d test_jsonb_constraints Table "public.test_jsonb_constraints" @@ -1014,7 +954,6 @@ Check constraints: "test_jsonb_constraint3" CHECK (JSON_VALUE(js::jsonb, '$."a"' RETURNING integer DEFAULT 12 ON EMPTY ERROR ON ERROR) > i) "test_jsonb_constraint4" CHECK (JSON_QUERY(js::jsonb, '$."a"' RETURNING jsonb WITH CONDITIONAL WRAPPER EMPTY OBJECT ON ERROR) < '[10]'::jsonb) "test_jsonb_constraint5" CHECK (JSON_QUERY(js::jsonb, '$."a"' RETURNING character(5) OMIT QUOTES EMPTY ARRAY ON EMPTY) > ('a'::bpchar COLLATE "C")) - "test_jsonb_constraint6" CHECK (JSON_EXISTS(js::jsonb, 'strict $."a"' RETURNING integer TRUE ON ERROR) < 2) SELECT check_clause FROM information_schema.check_constraints @@ -1022,13 +961,12 @@ WHERE constraint_name LIKE 'test_jsonb_constraint%' ORDER BY 1; check_clause ------------------------------------------------------------------------------------------------------------------------ - (JSON_EXISTS((js)::jsonb, 'strict $."a"' RETURNING integer TRUE ON ERROR) < 2) (JSON_QUERY((js)::jsonb, '$."a"' RETURNING character(5) OMIT QUOTES EMPTY ARRAY ON EMPTY) > ('a'::bpchar COLLATE "C")) (JSON_QUERY((js)::jsonb, '$."a"' RETURNING jsonb WITH CONDITIONAL WRAPPER EMPTY OBJECT ON ERROR) < '[10]'::jsonb) (JSON_VALUE((js)::jsonb, '$."a"' RETURNING integer DEFAULT 12 ON EMPTY ERROR ON ERROR) > i) (js IS JSON) JSON_EXISTS((js)::jsonb, '$."a"' PASSING (i + 5) AS int, (i)::text AS txt, ARRAY[1, 2, 3] AS arr) -(6 rows) +(5 rows) SELECT pg_get_expr(adbin, adrelid) FROM pg_attrdef diff --git a/src/test/regress/sql/jsonb_sqljson.sql b/src/test/regress/sql/jsonb_sqljson.sql index b3a32c09f6..b55889104a 100644 --- a/src/test/regress/sql/jsonb_sqljson.sql +++ b/src/test/regress/sql/jsonb_sqljson.sql @@ -31,22 +31,6 @@ SELECT JSON_EXISTS(jsonb '{"a": 1, "b": 2}', '$.* ? (@ > $x && @ < $y)' PASSING SELECT JSON_EXISTS(jsonb '1', '$ > 2'); SELECT JSON_EXISTS(jsonb '1', '$.a > 2' ERROR ON ERROR); --- extension: RETURNING clause -SELECT JSON_EXISTS(jsonb '1', '$[0]' RETURNING bool); -SELECT JSON_EXISTS(jsonb '1', '$[1]' RETURNING bool); -SELECT JSON_EXISTS(jsonb '1', '$[0]' RETURNING int); -SELECT JSON_EXISTS(jsonb '1', '$[1]' RETURNING int); -SELECT JSON_EXISTS(jsonb '1', '$[0]' RETURNING text); -SELECT JSON_EXISTS(jsonb '1', '$[1]' RETURNING text); -SELECT JSON_EXISTS(jsonb '1', 'strict $[1]' RETURNING text FALSE ON ERROR); -SELECT JSON_EXISTS(jsonb '1', '$[0]' RETURNING jsonb); -SELECT JSON_EXISTS(jsonb '1', '$[0]' RETURNING float4); -SELECT JSON_EXISTS(jsonb '1', '$[0]' RETURNING float4 FORMAT JSON); -- RETURNING FORMAT not allowed - --- RETUGNING pseudo-types not allowed -SELECT JSON_EXISTS(jsonb '1', '$[0]' RETURNING record); - - -- JSON_VALUE SELECT JSON_VALUE(NULL::jsonb, '$'); @@ -298,8 +282,6 @@ CREATE TABLE test_jsonb_constraints ( CHECK (JSON_QUERY(js::jsonb, '$.a' WITH CONDITIONAL WRAPPER EMPTY OBJECT ON ERROR) < jsonb '[10]') CONSTRAINT test_jsonb_constraint5 CHECK (JSON_QUERY(js::jsonb, '$.a' RETURNING char(5) OMIT QUOTES EMPTY ARRAY ON EMPTY) > 'a' COLLATE "C") - CONSTRAINT test_jsonb_constraint6 - CHECK (JSON_EXISTS(js::jsonb, 'strict $.a' RETURNING int TRUE ON ERROR) < 2) ); \d test_jsonb_constraints -- 2.35.3