public inbox for [email protected]help / color / mirror / Atom feed
Re: SQL:2023 JSON simplified accessor support 8+ messages / 7 participants [nested] [flat]
* Re: SQL:2023 JSON simplified accessor support @ 2024-09-26 15:45 Alexandra Wang <[email protected]> 0 siblings, 3 replies; 8+ messages in thread From: Alexandra Wang @ 2024-09-26 15:45 UTC (permalink / raw) To: Peter Eisentraut <[email protected]>; +Cc: pgsql-hackers; Andrew Dunstan <[email protected]> Hi, I didn’t run pgindent earlier, so here’s the updated version with the correct indentation. Hope this helps! Best, Alex Attachments: [application/octet-stream] v4-0001-Add-JSON-JSONB-simplified-accessor.patch (21.2K, ../../CAK98qZ13+TXB5fxHakxSJL7MYHARXBvdpzN6tymSrNLMZiin0Q@mail.gmail.com/2-v4-0001-Add-JSON-JSONB-simplified-accessor.patch) download | inline diff: From ea278dee92616f546bb9636bc4c0f7e36fba8136 Mon Sep 17 00:00:00 2001 From: Alexandra Wang <[email protected]> Date: Thu, 15 Aug 2024 02:11:33 -0700 Subject: [PATCH v4] Add JSON/JSONB simplified accessor This patch implements JSON/JSONB member accessor and JSON/JSONB array accessor as specified in SQL 2023. Specifically, the following sytaxes are added: 1. Simple dot-notation access to JSON and JSONB object fields 2. Subscripting for indexed access to JSON array elements Examples: -- Setup create table t(x int, y json); insert into t select 1, '{"a": 1, "b": 42}'::json; insert into t select 1, '{"a": 2, "b": {"c": 42}}'::json; insert into t select 1, '{"a": 3, "b": {"c": "42"}, "d":[11, 12]}'::json; -- Existing syntax predates the SQL standard: select (t.y)->'b' from t; select (t.y)->'b'->'c' from t; select (t.y)->'d'->0 from t; -- JSON simplified accessor specified by the SQL standard: select (t.y).b from t; select (t.y).b.c from t; select (t.y).d[0] from t; The SQL standard states that simplified access is equivalent to: JSON_QUERY (VEP, 'lax $.JC' WITH CONDITIONAL ARRAY WRAPPER NULL ON EMPTY NULL ON ERROR) where VEP is the <value expression primary> and JC is the <JSON simplified accessor op chain>. For example, the JSON_QUERY equalalence of the above queries is: select json_query(y, 'lax $.b' WITH CONDITIONAL ARRAY WRAPPER NULL ON EMPTY NULL ON ERROR) from t; select json_query(y, 'lax $.b.c' WITH CONDITIONAL ARRAY WRAPPER NULL ON EMPTY NULL ON ERROR) from t; select json_query(y, 'lax $.d[0]' WITH CONDITIONAL ARRAY WRAPPER NULL ON EMPTY NULL ON ERROR) from t; This implementation enables dot-notation access to JSON/JSONB object by making a syntatic sugar for the json_object_field "->" operator in ParseFuncOrColumn() for arg of JSON/JSONB type. Similarly, JSON array access via subscripting is enabled by creating an OpExpr for the existing "->" operator. Note that the JSON subscripting implementation is different from the JSONB subscripting counterpart, as the former leverages the "->" operator directly, while the latter uses the more generic SubscriptingRef interface. --- src/backend/parser/parse_expr.c | 8 ++- src/backend/parser/parse_func.c | 98 +++++++++++++++++++++++-- src/include/catalog/pg_operator.dat | 6 +- src/include/parser/parse_func.h | 3 + src/include/parser/parse_type.h | 1 + src/test/regress/expected/json.out | 107 ++++++++++++++++++++++++++++ src/test/regress/expected/jsonb.out | 86 ++++++++++++++++++++++ src/test/regress/sql/json.sql | 25 +++++++ src/test/regress/sql/jsonb.sql | 22 ++++++ 9 files changed, 347 insertions(+), 9 deletions(-) diff --git a/src/backend/parser/parse_expr.c b/src/backend/parser/parse_expr.c index 36c1b7a88f..c55582cde5 100644 --- a/src/backend/parser/parse_expr.c +++ b/src/backend/parser/parse_expr.c @@ -453,7 +453,13 @@ transformIndirection(ParseState *pstate, A_Indirection *ind) Node *n = lfirst(i); if (IsA(n, A_Indices)) - subscripts = lappend(subscripts, n); + if (exprType(result) == JSONOID) + result = ParseJsonSimplifiedAccessorArrayElement(pstate, + castNode(A_Indices, n), + result, + location); + else + subscripts = lappend(subscripts, n); else if (IsA(n, A_Star)) { ereport(ERROR, diff --git a/src/backend/parser/parse_func.c b/src/backend/parser/parse_func.c index 9b23344a3b..a13c001dd4 100644 --- a/src/backend/parser/parse_func.c +++ b/src/backend/parser/parse_func.c @@ -33,6 +33,8 @@ #include "utils/builtins.h" #include "utils/lsyscache.h" #include "utils/syscache.h" +#include "parser/parse_oper.h" +#include "catalog/pg_operator_d.h" /* Possible error codes from LookupFuncNameInternal */ @@ -48,6 +50,8 @@ static void unify_hypothetical_args(ParseState *pstate, static Oid FuncNameAsType(List *funcname); static Node *ParseComplexProjection(ParseState *pstate, const char *funcname, Node *first_arg, int location); +static Node *ParseJsonSimplifiedAccessorObjectField(ParseState *pstate, const char *funcname, + Node *first_arg, int location); static Oid LookupFuncNameInternal(ObjectType objtype, List *funcname, int nargs, const Oid *argtypes, bool include_out_arguments, bool missing_ok, @@ -226,17 +230,24 @@ ParseFuncOrColumn(ParseState *pstate, List *funcname, List *fargs, !func_variadic && argnames == NIL && list_length(funcname) == 1 && (actual_arg_types[0] == RECORDOID || - ISCOMPLEX(actual_arg_types[0]))); + ISCOMPLEX(actual_arg_types[0]) || + ISJSON(actual_arg_types[0]))); /* * If it's column syntax, check for column projection case first. */ if (could_be_projection && is_column) { - retval = ParseComplexProjection(pstate, - strVal(linitial(funcname)), - first_arg, - location); + if (ISJSON(actual_arg_types[0])) + retval = ParseJsonSimplifiedAccessorObjectField(pstate, + strVal(linitial(funcname)), + first_arg, + location); + else + retval = ParseComplexProjection(pstate, + strVal(linitial(funcname)), + first_arg, + location); if (retval) return retval; @@ -1902,6 +1913,83 @@ FuncNameAsType(List *funcname) return result; } +/* + * ParseJsonSimplifiedAccessorArrayElement - + * transform json subscript into json_array_element operator. + */ +Node * +ParseJsonSimplifiedAccessorArrayElement(ParseState *pstate, A_Indices *subscript, + Node *first_arg, int location) +{ + OpExpr *result; + Node *index; + + Assert(exprType(first_arg) == JSONOID); + + if (subscript->is_slice) + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("json subscript does not support slices")), + parser_errposition(pstate, location)); + + index = transformExpr(pstate, subscript->uidx, pstate->p_expr_kind); + if (!IsA(index, Const) || + castNode(Const, index)->consttype != INT4OID) + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("json subscript must be coercible to integer")), + parser_errposition(pstate, exprLocation(index))); + + result = makeNode(OpExpr); + result->opno = OID_JSON_ARRAY_ELEMENT_OP; + result->opresulttype = JSONOID; + result->opfuncid = get_opcode(result->opno); + result->args = list_make2(first_arg, index); + result->location = exprLocation(index); + + return (Node *) result; +} + +/* + * ParseJsonSimplifiedAccessorObjectField - + * handles function calls with a single argument that is of json type. + * If the function call is actually a column projection, return a suitably + * transformed expression tree. If not, return NULL. + */ +Node * +ParseJsonSimplifiedAccessorObjectField(ParseState *pstate, const char *funcname, + Node *first_arg, int location) +{ + OpExpr *result; + Node *rexpr; + + rexpr = (Node *) makeConst( + TEXTOID, + -1, + InvalidOid, + -1, + CStringGetTextDatum(funcname), + false, + false); + + result = makeNode(OpExpr); + if (exprType(first_arg) == JSONOID) + { + result->opno = OID_JSON_OBJECT_FIELD_OP; + result->opresulttype = JSONOID; + } + else + { + Assert(exprType(first_arg) == JSONBOID); + result->opno = OID_JSONB_OBJECT_FIELD_OP; + result->opresulttype = JSONBOID; + } + result->opfuncid = get_opcode(result->opno); + result->args = list_make2(first_arg, rexpr); + result->location = location; + return (Node *) result; +} + /* * ParseComplexProjection - * handles function calls with a single argument that is of complex type. diff --git a/src/include/catalog/pg_operator.dat b/src/include/catalog/pg_operator.dat index 0e7511dde1..e375c49252 100644 --- a/src/include/catalog/pg_operator.dat +++ b/src/include/catalog/pg_operator.dat @@ -3154,13 +3154,13 @@ oprname => '*', oprleft => 'anyrange', oprright => 'anyrange', oprresult => 'anyrange', oprcom => '*(anyrange,anyrange)', oprcode => 'range_intersect' }, -{ oid => '3962', descr => 'get json object field', +{ oid => '3962', oid_symbol => 'OID_JSON_OBJECT_FIELD_OP', descr => 'get json object field', oprname => '->', oprleft => 'json', oprright => 'text', oprresult => 'json', oprcode => 'json_object_field' }, { oid => '3963', descr => 'get json object field as text', oprname => '->>', oprleft => 'json', oprright => 'text', oprresult => 'text', oprcode => 'json_object_field_text' }, -{ oid => '3964', descr => 'get json array element', +{ oid => '3964', oid_symbol => 'OID_JSON_ARRAY_ELEMENT_OP', descr => 'get json array element', oprname => '->', oprleft => 'json', oprright => 'int4', oprresult => 'json', oprcode => 'json_array_element' }, { oid => '3965', descr => 'get json array element as text', @@ -3172,7 +3172,7 @@ { oid => '3967', descr => 'get value from json as text with path elements', oprname => '#>>', oprleft => 'json', oprright => '_text', oprresult => 'text', oprcode => 'json_extract_path_text' }, -{ oid => '3211', descr => 'get jsonb object field', +{ oid => '3211', oid_symbol => 'OID_JSONB_OBJECT_FIELD_OP', descr => 'get jsonb object field', oprname => '->', oprleft => 'jsonb', oprright => 'text', oprresult => 'jsonb', oprcode => 'jsonb_object_field' }, { oid => '3477', descr => 'get jsonb object field as text', diff --git a/src/include/parser/parse_func.h b/src/include/parser/parse_func.h index c7ba99dee7..6b7759cbc7 100644 --- a/src/include/parser/parse_func.h +++ b/src/include/parser/parse_func.h @@ -35,6 +35,9 @@ extern Node *ParseFuncOrColumn(ParseState *pstate, List *funcname, List *fargs, Node *last_srf, FuncCall *fn, bool proc_call, int location); +extern Node *ParseJsonSimplifiedAccessorArrayElement(ParseState *pstate, A_Indices *subscript, + Node *first_arg, int location); + extern FuncDetailCode func_get_detail(List *funcname, List *fargs, List *fargnames, int nargs, Oid *argtypes, diff --git a/src/include/parser/parse_type.h b/src/include/parser/parse_type.h index b62e7a6ce9..9c8b3bfb2f 100644 --- a/src/include/parser/parse_type.h +++ b/src/include/parser/parse_type.h @@ -57,5 +57,6 @@ extern bool parseTypeString(const char *str, Oid *typeid_p, int32 *typmod_p, /* true if typeid is composite, or domain over composite, but not RECORD */ #define ISCOMPLEX(typeid) (typeOrDomainTypeRelid(typeid) != InvalidOid) +#define ISJSON(typeid) (typeid == JSONOID || typeid == JSONBOID) #endif /* PARSE_TYPE_H */ diff --git a/src/test/regress/expected/json.out b/src/test/regress/expected/json.out index 96c40911cb..f6b7af3ecd 100644 --- a/src/test/regress/expected/json.out +++ b/src/test/regress/expected/json.out @@ -2716,3 +2716,110 @@ select ts_headline('[]'::json, tsquery('aaa & bbb')); [] (1 row) +-- simple dot notation +drop table if exists test_json_dot; +NOTICE: table "test_json_dot" does not exist, skipping +create table test_json_dot(id int, test_json json); +insert into test_json_dot select 1, '{"a": 1, "b": 42}'::json; +insert into test_json_dot select 1, '{"a": 2, "b": {"c": 42}}'::json; +insert into test_json_dot select 1, '{"a": 3, "b": {"c": "42"}, "d":[11, 12]}'::json; +insert into test_json_dot select 1, '{"a": 3, "b": {"c": "42"}, "d":[{"x": [11, 12]}, {"y": [21, 22]}]}'::json; +-- member object access +select (test_json_dot.test_json).b, json_query(test_json, 'lax $.b' WITH CONDITIONAL WRAPPER NULL ON EMPTY NULL ON ERROR) as expected from test_json_dot; + b | expected +-------------+------------- + 42 | 42 + {"c": 42} | {"c": 42} + {"c": "42"} | {"c": "42"} + {"c": "42"} | {"c": "42"} +(4 rows) + +select (test_json_dot.test_json).b.c, json_query(test_json, 'lax $.b.c' WITH CONDITIONAL WRAPPER NULL ON EMPTY NULL ON ERROR) as expected from test_json_dot; + c | expected +------+---------- + | + 42 | 42 + "42" | "42" + "42" | "42" +(4 rows) + +select (test_json_dot.test_json).d, json_query(test_json, 'lax $.d' WITH CONDITIONAL WRAPPER NULL ON EMPTY NULL ON ERROR) as expected from test_json_dot; + d | expected +------------------------------------+------------------------------------ + | + | + [11, 12] | [11, 12] + [{"x": [11, 12]}, {"y": [21, 22]}] | [{"x": [11, 12]}, {"y": [21, 22]}] +(4 rows) + +select (test_json_dot.test_json)."d", json_query(test_json, 'lax $.d' WITH CONDITIONAL WRAPPER NULL ON EMPTY NULL ON ERROR) as expected from test_json_dot; + d | expected +------------------------------------+------------------------------------ + | + | + [11, 12] | [11, 12] + [{"x": [11, 12]}, {"y": [21, 22]}] | [{"x": [11, 12]}, {"y": [21, 22]}] +(4 rows) + +select (test_json_dot.test_json).'d' from test_json_dot; +ERROR: syntax error at or near "'d'" +LINE 1: select (test_json_dot.test_json).'d' from test_json_dot; + ^ +select (test_json_dot.test_json)['d'] from test_json_dot; +ERROR: json subscript must be coercible to integer +LINE 1: select (test_json_dot.test_json)['d'] from test_json_dot; + ^ +-- array element access +select (test_json_dot.test_json).d->0 from test_json_dot; + ?column? +----------------- + + + 11 + {"x": [11, 12]} +(4 rows) + +select (test_json_dot.test_json).d[0], json_query(test_json, 'lax $.d[0]' WITH CONDITIONAL WRAPPER NULL ON EMPTY NULL ON ERROR) as expected from test_json_dot; + d | expected +-----------------+----------------- + | + | + 11 | 11 + {"x": [11, 12]} | {"x": [11, 12]} +(4 rows) + +select (test_json_dot.test_json).d[1], json_query(test_json, 'lax $.d[1]' WITH CONDITIONAL WRAPPER NULL ON EMPTY NULL ON ERROR) as expected from test_json_dot; + d | expected +-----------------+----------------- + | + | + 12 | 12 + {"y": [21, 22]} | {"y": [21, 22]} +(4 rows) + +select (test_json_dot.test_json).d[0:] from test_json_dot; +ERROR: json subscript does not support slices +LINE 1: select (test_json_dot.test_json).d[0:] from test_json_dot; + ^ +select (test_json_dot.test_json).d[0::int] from test_json_dot; + d +----------------- + + + 11 + {"x": [11, 12]} +(4 rows) + +select (test_json_dot.test_json).d[0::float] from test_json_dot; +ERROR: json subscript must be coercible to integer +LINE 1: select (test_json_dot.test_json).d[0::float] from test_json_... + ^ +select (test_json_dot.test_json).d[0].x[1], json_query(test_json, 'lax $.d[0].x[1]' WITH CONDITIONAL WRAPPER NULL ON EMPTY NULL ON ERROR) as expected from test_json_dot; + x | expected +----+---------- + | + | + | + 12 | 12 +(4 rows) + diff --git a/src/test/regress/expected/jsonb.out b/src/test/regress/expected/jsonb.out index 7d163a156e..954efe67b6 100644 --- a/src/test/regress/expected/jsonb.out +++ b/src/test/regress/expected/jsonb.out @@ -5715,3 +5715,89 @@ select '12345.0000000000000000000000000000000000000000000005'::jsonb::int8; 12345 (1 row) +-- simple dot notation +drop table if exists test_jsonb_dot; +NOTICE: table "test_jsonb_dot" does not exist, skipping +create table test_jsonb_dot(id int, test_jsonb jsonb); +insert into test_jsonb_dot select 1, '{"a": 1, "b": 42}'::json; +insert into test_jsonb_dot select 1, '{"a": 2, "b": {"c": 42}}'::json; +insert into test_jsonb_dot select 1, '{"a": 3, "b": {"c": "42"}, "d":[11, 12]}'::json; +-- member object access +select (test_jsonb_dot.test_jsonb).b from test_jsonb_dot; + b +------------- + 42 + {"c": 42} + {"c": "42"} +(3 rows) + +select (test_jsonb_dot.test_jsonb).b.c from test_jsonb_dot; + c +------ + + 42 + "42" +(3 rows) + +select (test_jsonb_dot.test_jsonb).d from test_jsonb_dot; + d +---------- + + + [11, 12] +(3 rows) + +select (test_jsonb_dot.test_jsonb)."d" from test_jsonb_dot; + d +---------- + + + [11, 12] +(3 rows) + +select (test_jsonb_dot.test_jsonb).'d' from test_jsonb_dot; +ERROR: syntax error at or near "'d'" +LINE 1: select (test_jsonb_dot.test_jsonb).'d' from test_jsonb_dot; + ^ +select (test_jsonb_dot.test_jsonb)['d'] from test_jsonb_dot; + test_jsonb +------------ + + + [11, 12] +(3 rows) + +-- array element access +select (test_jsonb_dot.test_jsonb).d[0] from test_jsonb_dot; + d +---- + + + 11 +(3 rows) + +select (test_jsonb_dot.test_jsonb).d[0:] from test_jsonb_dot; +ERROR: jsonb subscript does not support slices +LINE 1: select (test_jsonb_dot.test_jsonb).d[0:] from test_jsonb_dot... + ^ +select (test_jsonb_dot.test_jsonb).d[0::int] from test_jsonb_dot; + d +---- + + + 11 +(3 rows) + +select (test_jsonb_dot.test_jsonb).d[0::float] from test_jsonb_dot; +ERROR: subscript type double precision is not supported +LINE 1: select (test_jsonb_dot.test_jsonb).d[0::float] from test_jso... + ^ +HINT: jsonb subscript must be coercible to either integer or text. +select (test_jsonb_dot.test_jsonb).d[0].x[1] from test_jsonb_dot; + x +--- + + + +(3 rows) + diff --git a/src/test/regress/sql/json.sql b/src/test/regress/sql/json.sql index 8251f4f400..21450c4991 100644 --- a/src/test/regress/sql/json.sql +++ b/src/test/regress/sql/json.sql @@ -869,3 +869,28 @@ select ts_headline('english', '{"a": "aaa bbb", "b": {"c": "ccc ddd fff", "c1": select ts_headline('null'::json, tsquery('aaa & bbb')); select ts_headline('{}'::json, tsquery('aaa & bbb')); select ts_headline('[]'::json, tsquery('aaa & bbb')); + +-- simple dot notation +drop table if exists test_json_dot; +create table test_json_dot(id int, test_json json); +insert into test_json_dot select 1, '{"a": 1, "b": 42}'::json; +insert into test_json_dot select 1, '{"a": 2, "b": {"c": 42}}'::json; +insert into test_json_dot select 1, '{"a": 3, "b": {"c": "42"}, "d":[11, 12]}'::json; +insert into test_json_dot select 1, '{"a": 3, "b": {"c": "42"}, "d":[{"x": [11, 12]}, {"y": [21, 22]}]}'::json; + +-- member object access +select (test_json_dot.test_json).b, json_query(test_json, 'lax $.b' WITH CONDITIONAL WRAPPER NULL ON EMPTY NULL ON ERROR) as expected from test_json_dot; +select (test_json_dot.test_json).b.c, json_query(test_json, 'lax $.b.c' WITH CONDITIONAL WRAPPER NULL ON EMPTY NULL ON ERROR) as expected from test_json_dot; +select (test_json_dot.test_json).d, json_query(test_json, 'lax $.d' WITH CONDITIONAL WRAPPER NULL ON EMPTY NULL ON ERROR) as expected from test_json_dot; +select (test_json_dot.test_json)."d", json_query(test_json, 'lax $.d' WITH CONDITIONAL WRAPPER NULL ON EMPTY NULL ON ERROR) as expected from test_json_dot; +select (test_json_dot.test_json).'d' from test_json_dot; +select (test_json_dot.test_json)['d'] from test_json_dot; + +-- array element access +select (test_json_dot.test_json).d->0 from test_json_dot; +select (test_json_dot.test_json).d[0], json_query(test_json, 'lax $.d[0]' WITH CONDITIONAL WRAPPER NULL ON EMPTY NULL ON ERROR) as expected from test_json_dot; +select (test_json_dot.test_json).d[1], json_query(test_json, 'lax $.d[1]' WITH CONDITIONAL WRAPPER NULL ON EMPTY NULL ON ERROR) as expected from test_json_dot; +select (test_json_dot.test_json).d[0:] from test_json_dot; +select (test_json_dot.test_json).d[0::int] from test_json_dot; +select (test_json_dot.test_json).d[0::float] from test_json_dot; +select (test_json_dot.test_json).d[0].x[1], json_query(test_json, 'lax $.d[0].x[1]' WITH CONDITIONAL WRAPPER NULL ON EMPTY NULL ON ERROR) as expected from test_json_dot; diff --git a/src/test/regress/sql/jsonb.sql b/src/test/regress/sql/jsonb.sql index 5f0190d5a2..f095dc2bbe 100644 --- a/src/test/regress/sql/jsonb.sql +++ b/src/test/regress/sql/jsonb.sql @@ -1559,3 +1559,25 @@ select '12345.0000000000000000000000000000000000000000000005'::jsonb::float8; select '12345.0000000000000000000000000000000000000000000005'::jsonb::int2; select '12345.0000000000000000000000000000000000000000000005'::jsonb::int4; select '12345.0000000000000000000000000000000000000000000005'::jsonb::int8; + +-- simple dot notation +drop table if exists test_jsonb_dot; +create table test_jsonb_dot(id int, test_jsonb jsonb); +insert into test_jsonb_dot select 1, '{"a": 1, "b": 42}'::json; +insert into test_jsonb_dot select 1, '{"a": 2, "b": {"c": 42}}'::json; +insert into test_jsonb_dot select 1, '{"a": 3, "b": {"c": "42"}, "d":[11, 12]}'::json; + +-- member object access +select (test_jsonb_dot.test_jsonb).b from test_jsonb_dot; +select (test_jsonb_dot.test_jsonb).b.c from test_jsonb_dot; +select (test_jsonb_dot.test_jsonb).d from test_jsonb_dot; +select (test_jsonb_dot.test_jsonb)."d" from test_jsonb_dot; +select (test_jsonb_dot.test_jsonb).'d' from test_jsonb_dot; +select (test_jsonb_dot.test_jsonb)['d'] from test_jsonb_dot; + +-- array element access +select (test_jsonb_dot.test_jsonb).d[0] from test_jsonb_dot; +select (test_jsonb_dot.test_jsonb).d[0:] from test_jsonb_dot; +select (test_jsonb_dot.test_jsonb).d[0::int] from test_jsonb_dot; +select (test_jsonb_dot.test_jsonb).d[0::float] from test_jsonb_dot; +select (test_jsonb_dot.test_jsonb).d[0].x[1] from test_jsonb_dot; -- 2.39.5 (Apple Git-154) ^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: SQL:2023 JSON simplified accessor support @ 2024-09-26 17:16 Andrew Dunstan <[email protected]> parent: Alexandra Wang <[email protected]> 2 siblings, 0 replies; 8+ messages in thread From: Andrew Dunstan @ 2024-09-26 17:16 UTC (permalink / raw) To: Alexandra Wang <[email protected]>; Peter Eisentraut <[email protected]>; +Cc: pgsql-hackers On 2024-09-26 Th 11:45 AM, Alexandra Wang wrote: > Hi, > > I didn’t run pgindent earlier, so here’s the updated version with the > correct indentation. Hope this helps! This is a really nice feature, and provides a lot of expressive power for such a small piece of code. I notice this doesn't seem to work for domains over json and jsonb. andrew@~=# create domain json_d as json; CREATE DOMAIN andrew@~=# create table test_json_dot(id int, test_json json_d); CREATE TABLE andrew@~=# insert into test_json_dot select 1, '{"a": 1, "b": 42}'::json; INSERT 0 1 | | andrew@~=# select (test_json_dot.test_json).b, json_query(test_json, 'lax $.b' WITH CONDITIONAL WRAPPER NULL ON EMPTY NULL ON ERROR) as expected from test_json_dot; ERROR: column notation .b applied to type json_d, which is not a composite type LINE 1: select (test_json_dot.test_json).b, json_query(test_json, 'l... I'm not sure that's a terribly important use case, but we should probably make it work. If it's a domain we should get the basetype of the domain. There's some example code in src/backend/utils/adt/jsonfuncs.c cheers andrew -- Andrew Dunstan EDB: https://www.enterprisedb.com ^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: SQL:2023 JSON simplified accessor support @ 2024-09-27 09:49 David E. Wheeler <[email protected]> parent: Alexandra Wang <[email protected]> 2 siblings, 0 replies; 8+ messages in thread From: David E. Wheeler @ 2024-09-27 09:49 UTC (permalink / raw) To: Alexandra Wang <[email protected]>; +Cc: Peter Eisentraut <[email protected]>; pgsql-hackers; Andrew Dunstan <[email protected]> On Sep 26, 2024, at 16:45, Alexandra Wang <[email protected]> wrote: > I didn’t run pgindent earlier, so here’s the updated version with the > correct indentation. Hope this helps! Oh, nice! I don’t suppose the standard also has defined an operator equivalent to ->>, though, has it? I tend to want the text output far more often than a JSON scalar. Best, David ^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: SQL:2023 JSON simplified accessor support @ 2024-10-04 12:33 jian he <[email protected]> parent: Alexandra Wang <[email protected]> 2 siblings, 1 reply; 8+ messages in thread From: jian he @ 2024-10-04 12:33 UTC (permalink / raw) To: Alexandra Wang <[email protected]>; +Cc: Peter Eisentraut <[email protected]>; pgsql-hackers; Andrew Dunstan <[email protected]> On Thu, Sep 26, 2024 at 11:45 PM Alexandra Wang <[email protected]> wrote: > > Hi, > > I didn’t run pgindent earlier, so here’s the updated version with the > correct indentation. Hope this helps! > the attached patch solves the domain type issue, Andrew mentioned in the thread. I also added a test case: composite over jsonb domain type, it still works. for example: create domain json_d as jsonb; create type test as (a int, b json_d); create table t1(a test); insert into t1 select $$(1,"{""a"": 3, ""key1"": {""c"": ""42""}, ""key2"": [11, 12]}") $$; insert into t1 select $$ (1,"{""a"": 3, ""key1"": {""c"": ""42""}, ""key2"": [11, 12, {""x"": [31, 42]}]}") $$; select (t1.a).b.key2[2].x[1] from t1; select (t1.a).b.key1.c from t1; Attachments: [application/octet-stream] v4-0001-make-simplified-accessor-works-with-domain-ove.no-cfbot (7.3K, ../../CACJufxGKTN=bc73-Zhezz+7Gfy_sH8PyoVpd2Jf9wHgJ6ymmqw@mail.gmail.com/2-v4-0001-make-simplified-accessor-works-with-domain-ove.no-cfbot) download ^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: SQL:2023 JSON simplified accessor support @ 2024-11-07 21:57 Alexandra Wang <[email protected]> parent: jian he <[email protected]> 0 siblings, 1 reply; 8+ messages in thread From: Alexandra Wang @ 2024-11-07 21:57 UTC (permalink / raw) To: jian he <[email protected]>; Andrew Dunstan <[email protected]>; +Cc: Peter Eisentraut <[email protected]>; pgsql-hackers; [email protected] On Fri, Oct 4, 2024 at 7:33 AM jian he <[email protected]> wrote: > > On Thu, Sep 26, 2024 at 11:45 PM Alexandra Wang > <[email protected]> wrote: > > > > Hi, > > > > I didn’t run pgindent earlier, so here’s the updated version with the > > correct indentation. Hope this helps! > > > > the attached patch solves the domain type issue, Andrew mentioned in the thread. > > I also added a test case: composite over jsonb domain type, > > > it still works. for example: > create domain json_d as jsonb; > create type test as (a int, b json_d); > create table t1(a test); > insert into t1 select $$(1,"{""a"": 3, ""key1"": {""c"": ""42""}, > ""key2"": [11, 12]}") $$; > insert into t1 select $$ (1,"{""a"": 3, ""key1"": {""c"": ""42""}, > ""key2"": [11, 12, {""x"": [31, 42]}]}") $$; > > select (t1.a).b.key2[2].x[1] from t1; > select (t1.a).b.key1.c from t1; Thank you so much, Jian, for reviewing the patch and providing a fix! I’ve integrated your fix into the attached v5 patch. Inspired by your test case, I discovered another issue with domains over JSON: top-level JSON array access to a domain over JSON when the domain is a field of a composite type. Here’s an example: create domain json_d as json; create type test as (a int, b json_d); create table t1(a test); insert into t1 select $$ (1,"[{""a"": 3}, {""key1"": {""c"": ""42""}}, {""key2"": [11, 12]}]") $$; select (t1.a).b[0] from t1; The v5 patch includes the following updates: - Fixed the aforementioned issue and added more tests covering composite types with domains, nested domains, and arrays of domains over JSON/JSONB. - Refactored the logic for parsing JSON/JSONB object fields by moving it from ParseFuncOrColumn() to transformIndirection() for improved readability. The ParseFuncOrColumn() function is already handling both single-argument function calls and composite types, and it has other callers besides transformIndirection(). Best, Alex Attachments: [application/x-patch] v5-0001-Add-JSON-JSONB-simplified-accessor.patch (37.8K, ../../CAK98qZ3Hv6ndh2vaCyGUSdyE7AXTvCLUSYKTdHp2kaPDnp6S4w@mail.gmail.com/2-v5-0001-Add-JSON-JSONB-simplified-accessor.patch) download | inline diff: From 44d71c9364fb8e9e8c57a5390ea5c4a3f7e371e0 Mon Sep 17 00:00:00 2001 From: Alexandra Wang <[email protected]> Date: Thu, 7 Nov 2024 15:14:50 -0600 Subject: [PATCH v5] Add JSON/JSONB simplified accessor This patch implements JSON/JSONB member accessor and JSON/JSONB array accessor as specified in SQL 2023. Specifically, the following sytaxes are added: 1. Simple dot-notation access to JSON and JSONB object fields 2. Subscripting for indexed access to JSON array elements Examples: -- Setup create table t(x int, y json); insert into t select 1, '{"a": 1, "b": 42}'::json; insert into t select 1, '{"a": 2, "b": {"c": 42}}'::json; insert into t select 1, '{"a": 3, "b": {"c": "42"}, "d":[11, 12]}'::json; -- Existing syntax predates the SQL standard: select (t.y)->'b' from t; select (t.y)->'b'->'c' from t; select (t.y)->'d'->0 from t; -- JSON simplified accessor specified by the SQL standard: select (t.y).b from t; select (t.y).b.c from t; select (t.y).d[0] from t; The SQL standard states that simplified access is equivalent to: JSON_QUERY (VEP, 'lax $.JC' WITH CONDITIONAL ARRAY WRAPPER NULL ON EMPTY NULL ON ERROR) where VEP is the <value expression primary> and JC is the <JSON simplified accessor op chain>. For example, the JSON_QUERY equalalence of the above queries is: select json_query(y, 'lax $.b' WITH CONDITIONAL ARRAY WRAPPER NULL ON EMPTY NULL ON ERROR) from t; select json_query(y, 'lax $.b.c' WITH CONDITIONAL ARRAY WRAPPER NULL ON EMPTY NULL ON ERROR) from t; select json_query(y, 'lax $.d[0]' WITH CONDITIONAL ARRAY WRAPPER NULL ON EMPTY NULL ON ERROR) from t; This implementation enables dot-notation access to JSON/JSONB object by making a syntatic sugar for the json_object_field "->" operator in ParseFuncOrColumn() for arg of JSON/JSONB type. Similarly, JSON array access via subscripting is enabled by creating an OpExpr for the existing "->" operator. Note that the JSON subscripting implementation is different from the JSONB subscripting counterpart, as the former leverages the "->" operator directly, while the latter uses the more generic SubscriptingRef interface. Domains over JSON/JSONB types are also suppported. --- src/backend/parser/parse_expr.c | 37 ++++- src/backend/parser/parse_func.c | 85 +++++++++- src/include/catalog/pg_operator.dat | 6 +- src/include/parser/parse_func.h | 4 + src/test/regress/expected/json.out | 246 ++++++++++++++++++++++++++++ src/test/regress/expected/jsonb.out | 228 ++++++++++++++++++++++++++ src/test/regress/sql/json.sql | 86 ++++++++++ src/test/regress/sql/jsonb.sql | 83 ++++++++++ 8 files changed, 763 insertions(+), 12 deletions(-) diff --git a/src/backend/parser/parse_expr.c b/src/backend/parser/parse_expr.c index c2806297aa..9a0ef59b4c 100644 --- a/src/backend/parser/parse_expr.c +++ b/src/backend/parser/parse_expr.c @@ -451,7 +451,16 @@ transformIndirection(ParseState *pstate, A_Indirection *ind) Node *n = lfirst(i); if (IsA(n, A_Indices)) - subscripts = lappend(subscripts, n); + { + if (exprType(result) == JSONOID || getBaseType(exprType(result)) == JSONOID) + result = ParseJsonSimplifiedAccessorArrayElement(pstate, + castNode(A_Indices, n), + result, + location); + else + subscripts = lappend(subscripts, n); + } + else if (IsA(n, A_Star)) { ereport(ERROR, @@ -462,6 +471,8 @@ transformIndirection(ParseState *pstate, A_Indirection *ind) else { Node *newresult; + Oid result_typid; + Oid result_basetypid; Assert(IsA(n, String)); @@ -475,13 +486,23 @@ transformIndirection(ParseState *pstate, A_Indirection *ind) false); subscripts = NIL; - newresult = ParseFuncOrColumn(pstate, - list_make1(n), - list_make1(result), - last_srf, - NULL, - false, - location); + result_typid = exprType(result); + result_basetypid = (result_typid == JSONOID || result_typid == JSONBOID) ? + result_typid : getBaseType(result_typid); + + if (result_basetypid == JSONOID || result_basetypid == JSONBOID) + newresult = ParseJsonSimplifiedAccessorObjectField(pstate, + strVal(n), + result, + location, result_basetypid); + else + newresult = ParseFuncOrColumn(pstate, + list_make1(n), + list_make1(result), + last_srf, + NULL, + false, + location); if (newresult == NULL) unknown_attribute(pstate, result, strVal(n), location); result = newresult; diff --git a/src/backend/parser/parse_func.c b/src/backend/parser/parse_func.c index 9b23344a3b..1f53736ce0 100644 --- a/src/backend/parser/parse_func.c +++ b/src/backend/parser/parse_func.c @@ -33,6 +33,8 @@ #include "utils/builtins.h" #include "utils/lsyscache.h" #include "utils/syscache.h" +#include "parser/parse_oper.h" +#include "catalog/pg_operator_d.h" /* Possible error codes from LookupFuncNameInternal */ @@ -53,7 +55,6 @@ static Oid LookupFuncNameInternal(ObjectType objtype, List *funcname, bool include_out_arguments, bool missing_ok, FuncLookupError *lookupError); - /* * Parse a function call * @@ -1902,6 +1903,88 @@ FuncNameAsType(List *funcname) return result; } +/* + * ParseJsonSimplifiedAccessorArrayElement - + * transform json subscript into json_array_element operator. + */ +Node * +ParseJsonSimplifiedAccessorArrayElement(ParseState *pstate, A_Indices *subscript, + Node *first_arg, int location) +{ + OpExpr *result; + Node *index; + + if (exprType(first_arg) != JSONOID && getBaseType(exprType(first_arg)) != JSONOID) + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("json subscript does not support type oid: %u", + exprType(first_arg))), + parser_errposition(pstate, location)); + + if (subscript->is_slice) + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("json subscript does not support slices")), + parser_errposition(pstate, location)); + + index = transformExpr(pstate, subscript->uidx, pstate->p_expr_kind); + if (!IsA(index, Const) || + castNode(Const, index)->consttype != INT4OID) + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("json subscript must be coercible to integer")), + parser_errposition(pstate, exprLocation(index))); + + result = makeNode(OpExpr); + result->opno = OID_JSON_ARRAY_ELEMENT_OP; + result->opresulttype = JSONOID; + result->opfuncid = get_opcode(result->opno); + result->args = list_make2(first_arg, index); + result->location = exprLocation(index); + + return (Node *) result; +} + +/* + * ParseJsonSimplifiedAccessorObjectField - + * handles function calls with a single argument that is of json type. + * If the function call is actually a column projection, return a suitably + * transformed expression tree. If not, return NULL. + */ +Node * +ParseJsonSimplifiedAccessorObjectField(ParseState *pstate, const char *funcname, + Node *first_arg, int location, Oid basetypid) +{ + OpExpr *result; + Node *rexpr; + + result = makeNode(OpExpr); + result->opresulttype = basetypid; + switch (basetypid) + { + case JSONOID: + result->opno = OID_JSON_OBJECT_FIELD_OP; + break; + case JSONBOID: + result->opno = OID_JSONB_OBJECT_FIELD_OP; + break; + default: + elog(ERROR, "unsupported type OID: %u", basetypid); + } + result->opfuncid = get_opcode(result->opno); + rexpr = (Node *) makeConst( + TEXTOID, + -1, + InvalidOid, + -1, + CStringGetTextDatum(funcname), + false, + false); + result->args = list_make2(first_arg, rexpr); + result->location = location; + return (Node *) result; +} + /* * ParseComplexProjection - * handles function calls with a single argument that is of complex type. diff --git a/src/include/catalog/pg_operator.dat b/src/include/catalog/pg_operator.dat index 0e7511dde1..e375c49252 100644 --- a/src/include/catalog/pg_operator.dat +++ b/src/include/catalog/pg_operator.dat @@ -3154,13 +3154,13 @@ oprname => '*', oprleft => 'anyrange', oprright => 'anyrange', oprresult => 'anyrange', oprcom => '*(anyrange,anyrange)', oprcode => 'range_intersect' }, -{ oid => '3962', descr => 'get json object field', +{ oid => '3962', oid_symbol => 'OID_JSON_OBJECT_FIELD_OP', descr => 'get json object field', oprname => '->', oprleft => 'json', oprright => 'text', oprresult => 'json', oprcode => 'json_object_field' }, { oid => '3963', descr => 'get json object field as text', oprname => '->>', oprleft => 'json', oprright => 'text', oprresult => 'text', oprcode => 'json_object_field_text' }, -{ oid => '3964', descr => 'get json array element', +{ oid => '3964', oid_symbol => 'OID_JSON_ARRAY_ELEMENT_OP', descr => 'get json array element', oprname => '->', oprleft => 'json', oprright => 'int4', oprresult => 'json', oprcode => 'json_array_element' }, { oid => '3965', descr => 'get json array element as text', @@ -3172,7 +3172,7 @@ { oid => '3967', descr => 'get value from json as text with path elements', oprname => '#>>', oprleft => 'json', oprright => '_text', oprresult => 'text', oprcode => 'json_extract_path_text' }, -{ oid => '3211', descr => 'get jsonb object field', +{ oid => '3211', oid_symbol => 'OID_JSONB_OBJECT_FIELD_OP', descr => 'get jsonb object field', oprname => '->', oprleft => 'jsonb', oprright => 'text', oprresult => 'jsonb', oprcode => 'jsonb_object_field' }, { oid => '3477', descr => 'get jsonb object field as text', diff --git a/src/include/parser/parse_func.h b/src/include/parser/parse_func.h index c7ba99dee7..64a600079c 100644 --- a/src/include/parser/parse_func.h +++ b/src/include/parser/parse_func.h @@ -34,6 +34,10 @@ typedef enum extern Node *ParseFuncOrColumn(ParseState *pstate, List *funcname, List *fargs, Node *last_srf, FuncCall *fn, bool proc_call, int location); +extern Node *ParseJsonSimplifiedAccessorObjectField(ParseState *pstate, const char *funcname, + Node *first_arg, int location, Oid basetypid); +extern Node *ParseJsonSimplifiedAccessorArrayElement(ParseState *pstate, A_Indices *subscript, + Node *first_arg, int location); extern FuncDetailCode func_get_detail(List *funcname, List *fargs, List *fargnames, diff --git a/src/test/regress/expected/json.out b/src/test/regress/expected/json.out index 96c40911cb..9da7dba068 100644 --- a/src/test/regress/expected/json.out +++ b/src/test/regress/expected/json.out @@ -2716,3 +2716,249 @@ select ts_headline('[]'::json, tsquery('aaa & bbb')); [] (1 row) +-- simple dot notation +drop table if exists test_json_dot; +NOTICE: table "test_json_dot" does not exist, skipping +create table test_json_dot(id int, test_json json); +insert into test_json_dot select 1, '{"a": 1, "b": 42}'::json; +insert into test_json_dot select 1, '{"a": 2, "b": {"c": 42}}'::json; +insert into test_json_dot select 1, '{"a": 3, "b": {"c": "42"}, "d":[11, 12]}'::json; +insert into test_json_dot select 1, '{"a": 3, "b": {"c": "42"}, "d":[{"x": [11, 12]}, {"y": [21, 22]}]}'::json; +-- member object access +select (test_json_dot.test_json).b, json_query(test_json, 'lax $.b' WITH CONDITIONAL WRAPPER NULL ON EMPTY NULL ON ERROR) as expected from test_json_dot; + b | expected +-------------+------------- + 42 | 42 + {"c": 42} | {"c": 42} + {"c": "42"} | {"c": "42"} + {"c": "42"} | {"c": "42"} +(4 rows) + +select (test_json_dot.test_json).b.c, json_query(test_json, 'lax $.b.c' WITH CONDITIONAL WRAPPER NULL ON EMPTY NULL ON ERROR) as expected from test_json_dot; + c | expected +------+---------- + | + 42 | 42 + "42" | "42" + "42" | "42" +(4 rows) + +select (test_json_dot.test_json).d, json_query(test_json, 'lax $.d' WITH CONDITIONAL WRAPPER NULL ON EMPTY NULL ON ERROR) as expected from test_json_dot; + d | expected +------------------------------------+------------------------------------ + | + | + [11, 12] | [11, 12] + [{"x": [11, 12]}, {"y": [21, 22]}] | [{"x": [11, 12]}, {"y": [21, 22]}] +(4 rows) + +select (test_json_dot.test_json)."d", json_query(test_json, 'lax $.d' WITH CONDITIONAL WRAPPER NULL ON EMPTY NULL ON ERROR) as expected from test_json_dot; + d | expected +------------------------------------+------------------------------------ + | + | + [11, 12] | [11, 12] + [{"x": [11, 12]}, {"y": [21, 22]}] | [{"x": [11, 12]}, {"y": [21, 22]}] +(4 rows) + +select (test_json_dot.test_json).'d' from test_json_dot; +ERROR: syntax error at or near "'d'" +LINE 1: select (test_json_dot.test_json).'d' from test_json_dot; + ^ +select (test_json_dot.test_json)['d'] from test_json_dot; +ERROR: json subscript must be coercible to integer +LINE 1: select (test_json_dot.test_json)['d'] from test_json_dot; + ^ +-- wildcard access is not supported +select (test_json_dot.test_json).* from test_json_dot; +ERROR: type json is not composite +-- array element access +select (test_json_dot.test_json).d->0 from test_json_dot; + ?column? +----------------- + + + 11 + {"x": [11, 12]} +(4 rows) + +select (test_json_dot.test_json).d[0], json_query(test_json, 'lax $.d[0]' WITH CONDITIONAL WRAPPER NULL ON EMPTY NULL ON ERROR) as expected from test_json_dot; + d | expected +-----------------+----------------- + | + | + 11 | 11 + {"x": [11, 12]} | {"x": [11, 12]} +(4 rows) + +select (test_json_dot.test_json).d[1], json_query(test_json, 'lax $.d[1]' WITH CONDITIONAL WRAPPER NULL ON EMPTY NULL ON ERROR) as expected from test_json_dot; + d | expected +-----------------+----------------- + | + | + 12 | 12 + {"y": [21, 22]} | {"y": [21, 22]} +(4 rows) + +select (test_json_dot.test_json).d[0:] from test_json_dot; +ERROR: json subscript does not support slices +LINE 1: select (test_json_dot.test_json).d[0:] from test_json_dot; + ^ +select (test_json_dot.test_json).d[0::int] from test_json_dot; + d +----------------- + + + 11 + {"x": [11, 12]} +(4 rows) + +select (test_json_dot.test_json).d[0::float] from test_json_dot; +ERROR: json subscript must be coercible to integer +LINE 1: select (test_json_dot.test_json).d[0::float] from test_json_... + ^ +select (test_json_dot.test_json).d[0].x[1], json_query(test_json, 'lax $.d[0].x[1]' WITH CONDITIONAL WRAPPER NULL ON EMPTY NULL ON ERROR) as expected from test_json_dot; + x | expected +----+---------- + | + | + | + 12 | 12 +(4 rows) + +-- complex type with domain over json +create domain json_d as json; +create type comp_json as (a int, b json_d); +create table test_json_domain_dot(a comp_json); +insert into test_json_domain_dot select $$ (1,"{""a"": 3, ""key1"": {""c"": ""42""}, ""key2"": [11, 12]}") $$; +insert into test_json_domain_dot select $$ (1,"{""a"": 3, ""key1"": {""c"": ""42""}, ""key2"": [11, 12, {""x"": [31, 42]}]}") $$; +--object access +select (test_json_domain_dot.a).b.key1.c from test_json_domain_dot; + c +------ + "42" + "42" +(2 rows) + +select (test_json_domain_dot.a).b.key2 from test_json_domain_dot; + key2 +--------------------------- + [11, 12] + [11, 12, {"x": [31, 42]}] +(2 rows) + +select (test_json_domain_dot.a).b.key2[0] from test_json_domain_dot; + key2 +------ + 11 + 11 +(2 rows) + +select (test_json_domain_dot.a).b.key2[0::text] from test_json_domain_dot; +ERROR: json subscript must be coercible to integer +LINE 1: select (test_json_domain_dot.a).b.key2[0::text] from test_js... + ^ +select (test_json_domain_dot.a).b.key2[2].x[1] from test_json_domain_dot; + x +---- + + 42 +(2 rows) + +-- array access +insert into test_json_domain_dot select $$ (1,"[{""a"": 3}, {""key1"": {""c"": ""42""}}, {""key2"": [11, 12]}]") $$; +select (test_json_domain_dot.a).b[0] from test_json_domain_dot; + b +---------- + + + {"a": 3} +(3 rows) + +select (test_json_domain_dot.a).b[0:] from test_json_domain_dot; +ERROR: json subscript does not support slices +LINE 1: select (test_json_domain_dot.a).b[0:] from test_json_domain_... + ^ +drop table test_json_domain_dot cascade; +drop type comp_json cascade; +drop domain json_d cascade; +-- nested domains over json +CREATE DOMAIN json_with_name AS json + CHECK ( + -- check that JSON has a "name" field and that it is a string + json_typeof(VALUE->'name') = 'string' + ); +CREATE DOMAIN json_with_name_and_email AS json_with_name + CHECK ( + -- ensure that if "email" exists, it follows a simple email format + VALUE->'email' IS NULL OR (VALUE->>'email' ~ '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$') + ); +CREATE DOMAIN json_user_profile AS json_with_name_and_email + CHECK ( + -- ensure that if "phone" exists, it follows a basic phone format + VALUE->'phone' IS NULL OR (VALUE->>'phone' ~ '^\+\d{1,3}-\d{3}-\d{3}-\d{4}$') + ); +CREATE TABLE json_users (id SERIAL PRIMARY KEY, profile json_user_profile); +INSERT INTO json_users (profile) VALUES ('{"name": "Alice", "email": "[email protected]", "phone": "+1-123-456-7890"}'); +INSERT INTO json_users (profile) VALUES ('{"name": "Bob", "email": "[email protected]", "phone": "+9-876-543-3210", "address": [123, "1st street", "New York", "New York", 12345]}'); +SELECT (json_users.profile).name from json_users; + name +--------- + "Alice" + "Bob" +(2 rows) + +SELECT (json_users.profile).email from json_users; + email +--------------------- + "[email protected]" + "[email protected]" +(2 rows) + +SELECT (json_users.profile).phone from json_users; + phone +------------------- + "+1-123-456-7890" + "+9-876-543-3210" +(2 rows) + +SELECT (json_users.profile).address from json_users; + address +---------------------------------------------------- + + [123, "1st street", "New York", "New York", 12345] +(2 rows) + +SELECT (json_users.profile).address[3] from json_users; + address +------------ + + "New York" +(2 rows) + +-- array of nested domains over json +CREATE TABLE json_user_arrs (id SERIAL PRIMARY KEY, profiles json_user_profile[]); +INSERT INTO json_user_arrs (profiles) VALUES (ARRAY['{"name": "Alice", "email": "[email protected]", "phone": "+1-123-456-7890"}'::json_user_profile, '{"name": "Bob", "email": "[email protected]", "phone": "+9-876-543-3210", "address": [123, "1st street", "New York", "New York", 12345]}'::json_user_profile]); +SELECT json_user_arrs.profiles[1] from json_user_arrs; + profiles +----------------------------------------------------------------------------- + {"name": "Alice", "email": "[email protected]", "phone": "+1-123-456-7890"} +(1 row) + +SELECT json_user_arrs.profiles[2] from json_user_arrs; + profiles +---------------------------------------------------------------------------------------------------------------------------------------- + {"name": "Bob", "email": "[email protected]", "phone": "+9-876-543-3210", "address": [123, "1st street", "New York", "New York", 12345]} +(1 row) + +SELECT json_user_arrs.profiles[2].address[0] from json_user_arrs; + address +--------- + 123 +(1 row) + +drop table json_users; +drop table json_user_arrs; +drop domain json_user_profile; +drop domain json_with_name_and_email; +drop domain json_with_name; diff --git a/src/test/regress/expected/jsonb.out b/src/test/regress/expected/jsonb.out index 7d163a156e..357b2f4356 100644 --- a/src/test/regress/expected/jsonb.out +++ b/src/test/regress/expected/jsonb.out @@ -5715,3 +5715,231 @@ select '12345.0000000000000000000000000000000000000000000005'::jsonb::int8; 12345 (1 row) +-- simple dot notation +drop table if exists test_jsonb_dot; +NOTICE: table "test_jsonb_dot" does not exist, skipping +create table test_jsonb_dot(id int, test_jsonb jsonb); +insert into test_jsonb_dot select 1, '{"a": 1, "b": 42}'::json; +insert into test_jsonb_dot select 1, '{"a": 2, "b": {"c": 42}}'::json; +insert into test_jsonb_dot select 1, '{"a": 3, "b": {"c": "42"}, "d":[11, 12]}'::json; +-- member object access +select (test_jsonb_dot.test_jsonb).b from test_jsonb_dot; + b +------------- + 42 + {"c": 42} + {"c": "42"} +(3 rows) + +select (test_jsonb_dot.test_jsonb).b.c from test_jsonb_dot; + c +------ + + 42 + "42" +(3 rows) + +select (test_jsonb_dot.test_jsonb).d from test_jsonb_dot; + d +---------- + + + [11, 12] +(3 rows) + +select (test_jsonb_dot.test_jsonb)."d" from test_jsonb_dot; + d +---------- + + + [11, 12] +(3 rows) + +select (test_jsonb_dot.test_jsonb).'d' from test_jsonb_dot; +ERROR: syntax error at or near "'d'" +LINE 1: select (test_jsonb_dot.test_jsonb).'d' from test_jsonb_dot; + ^ +select (test_jsonb_dot.test_jsonb)['d'] from test_jsonb_dot; + test_jsonb +------------ + + + [11, 12] +(3 rows) + +-- array element access +select (test_jsonb_dot.test_jsonb).d[0] from test_jsonb_dot; + d +---- + + + 11 +(3 rows) + +select (test_jsonb_dot.test_jsonb).d[0:] from test_jsonb_dot; +ERROR: jsonb subscript does not support slices +LINE 1: select (test_jsonb_dot.test_jsonb).d[0:] from test_jsonb_dot... + ^ +select (test_jsonb_dot.test_jsonb).d[0::int] from test_jsonb_dot; + d +---- + + + 11 +(3 rows) + +select (test_jsonb_dot.test_jsonb).d[0::float] from test_jsonb_dot; +ERROR: subscript type double precision is not supported +LINE 1: select (test_jsonb_dot.test_jsonb).d[0::float] from test_jso... + ^ +HINT: jsonb subscript must be coercible to either integer or text. +select (test_jsonb_dot.test_jsonb).d[0].x[1] from test_jsonb_dot; + x +--- + + + +(3 rows) + +-- wildcard access is not supported +select (test_jsonb_dot.test_jsonb).* from test_jsonb_dot; +ERROR: type jsonb is not composite +-- complex type with domain over jsonb +create domain jsonb_d as jsonb; +create type comp_jsonb as (a int, b jsonb_d); +create table test_jsonb_domain_dot(a comp_jsonb); +insert into test_jsonb_domain_dot select $$ (1,"{""a"": 3, ""key1"": {""c"": ""42""}, ""key2"": [11, 12]}") $$; +insert into test_jsonb_domain_dot select $$ (1,"{""a"": 3, ""key1"": {""c"": ""42""}, ""key2"": [11, 12, {""x"": [31, 42]}]}") $$; +-- object access +select (test_jsonb_domain_dot.a).b.key1.c from test_jsonb_domain_dot; + c +------ + "42" + "42" +(2 rows) + +select (test_jsonb_domain_dot.a).b.key2 from test_jsonb_domain_dot; + key2 +--------------------------- + [11, 12] + [11, 12, {"x": [31, 42]}] +(2 rows) + +select (test_jsonb_domain_dot.a).b.key2[0] from test_jsonb_domain_dot; + key2 +------ + 11 + 11 +(2 rows) + +select (test_jsonb_domain_dot.a).b.key2[0::text] from test_jsonb_domain_dot; + key2 +------ + 11 + 11 +(2 rows) + +select (test_jsonb_domain_dot.a).b.key2[2].x[1] from test_jsonb_domain_dot; + x +---- + + 42 +(2 rows) + +-- array access +insert into test_jsonb_domain_dot select $$ (1,"[{""a"": 3}, {""key1"": {""c"": ""42""}}, {""key2"": [11, 12]}]") $$; +select (test_jsonb_domain_dot.a).b[0] from test_jsonb_domain_dot; + b +---------- + + + {"a": 3} +(3 rows) + +select (test_jsonb_domain_dot.a).b[0:] from test_jsonb_domain_dot; +ERROR: jsonb subscript does not support slices +LINE 1: select (test_jsonb_domain_dot.a).b[0:] from test_jsonb_domai... + ^ +drop table test_jsonb_domain_dot cascade; +drop type comp_jsonb cascade; +drop domain jsonb_d cascade; +-- nested domains over jsonb +CREATE DOMAIN jsonb_with_name AS JSONB + CHECK ( + -- check that JSON has a "name" field and that it is a string + VALUE ? 'name' AND jsonb_typeof(VALUE->'name') = 'string' + ); +CREATE DOMAIN jsonb_with_name_and_email AS jsonb_with_name + CHECK ( + -- ensure that if "email" exists, it follows a simple email format + NOT VALUE ? 'email' OR (VALUE->>'email' ~ '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$') + ); +CREATE DOMAIN jsonb_user_profile AS jsonb_with_name_and_email + CHECK ( + -- ensure that if "phone" exists, it follows a basic phone format + NOT VALUE ? 'phone' OR (VALUE->>'phone' ~ '^\+\d{1,3}-\d{3}-\d{3}-\d{4}$') + ); +CREATE TABLE jsonb_users (id SERIAL PRIMARY KEY, profile jsonb_user_profile); +INSERT INTO jsonb_users (profile) VALUES ('{"name": "Alice", "email": "[email protected]", "phone": "+1-123-456-7890"}'); +INSERT INTO jsonb_users (profile) VALUES ('{"name": "Bob", "email": "[email protected]", "phone": "+9-876-543-3210", "address": [123, "1st street", "New York", "New York", 12345]}'); +SELECT (jsonb_users.profile).name from jsonb_users; + name +--------- + "Alice" + "Bob" +(2 rows) + +SELECT (jsonb_users.profile).email from jsonb_users; + email +--------------------- + "[email protected]" + "[email protected]" +(2 rows) + +SELECT (jsonb_users.profile).phone from jsonb_users; + phone +------------------- + "+1-123-456-7890" + "+9-876-543-3210" +(2 rows) + +SELECT (jsonb_users.profile).address from jsonb_users; + address +---------------------------------------------------- + + [123, "1st street", "New York", "New York", 12345] +(2 rows) + +SELECT (jsonb_users.profile).address[3] from jsonb_users; + address +------------ + + "New York" +(2 rows) + +-- array of nested domains over jsonb +CREATE TABLE jsonb_user_arrs (id SERIAL PRIMARY KEY, profiles jsonb_user_profile[]); +INSERT INTO jsonb_user_arrs (profiles) VALUES (ARRAY['{"name": "Alice", "email": "[email protected]", "phone": "+1-123-456-7890"}'::jsonb_user_profile, '{"name": "Bob", "email": "[email protected]", "phone": "+9-876-543-3210", "address": [123, "1st street", "New York", "New York", 12345]}'::jsonb_user_profile]); +SELECT jsonb_user_arrs.profiles[1] from jsonb_user_arrs; + profiles +----------------------------------------------------------------------------- + {"name": "Alice", "email": "[email protected]", "phone": "+1-123-456-7890"} +(1 row) + +SELECT jsonb_user_arrs.profiles[2] from jsonb_user_arrs; + profiles +---------------------------------------------------------------------------------------------------------------------------------------- + {"name": "Bob", "email": "[email protected]", "phone": "+9-876-543-3210", "address": [123, "1st street", "New York", "New York", 12345]} +(1 row) + +SELECT jsonb_user_arrs.profiles[2].address[0] from jsonb_user_arrs; + address +--------- + 123 +(1 row) + +drop table jsonb_users; +drop table jsonb_user_arrs; +drop domain jsonb_user_profile; +drop domain jsonb_with_name_and_email; +drop domain jsonb_with_name; diff --git a/src/test/regress/sql/json.sql b/src/test/regress/sql/json.sql index 8251f4f400..25fb1259ab 100644 --- a/src/test/regress/sql/json.sql +++ b/src/test/regress/sql/json.sql @@ -869,3 +869,89 @@ select ts_headline('english', '{"a": "aaa bbb", "b": {"c": "ccc ddd fff", "c1": select ts_headline('null'::json, tsquery('aaa & bbb')); select ts_headline('{}'::json, tsquery('aaa & bbb')); select ts_headline('[]'::json, tsquery('aaa & bbb')); + +-- simple dot notation +drop table if exists test_json_dot; +create table test_json_dot(id int, test_json json); +insert into test_json_dot select 1, '{"a": 1, "b": 42}'::json; +insert into test_json_dot select 1, '{"a": 2, "b": {"c": 42}}'::json; +insert into test_json_dot select 1, '{"a": 3, "b": {"c": "42"}, "d":[11, 12]}'::json; +insert into test_json_dot select 1, '{"a": 3, "b": {"c": "42"}, "d":[{"x": [11, 12]}, {"y": [21, 22]}]}'::json; + +-- member object access +select (test_json_dot.test_json).b, json_query(test_json, 'lax $.b' WITH CONDITIONAL WRAPPER NULL ON EMPTY NULL ON ERROR) as expected from test_json_dot; +select (test_json_dot.test_json).b.c, json_query(test_json, 'lax $.b.c' WITH CONDITIONAL WRAPPER NULL ON EMPTY NULL ON ERROR) as expected from test_json_dot; +select (test_json_dot.test_json).d, json_query(test_json, 'lax $.d' WITH CONDITIONAL WRAPPER NULL ON EMPTY NULL ON ERROR) as expected from test_json_dot; +select (test_json_dot.test_json)."d", json_query(test_json, 'lax $.d' WITH CONDITIONAL WRAPPER NULL ON EMPTY NULL ON ERROR) as expected from test_json_dot; +select (test_json_dot.test_json).'d' from test_json_dot; +select (test_json_dot.test_json)['d'] from test_json_dot; + +-- wildcard access is not supported +select (test_json_dot.test_json).* from test_json_dot; + +-- array element access +select (test_json_dot.test_json).d->0 from test_json_dot; +select (test_json_dot.test_json).d[0], json_query(test_json, 'lax $.d[0]' WITH CONDITIONAL WRAPPER NULL ON EMPTY NULL ON ERROR) as expected from test_json_dot; +select (test_json_dot.test_json).d[1], json_query(test_json, 'lax $.d[1]' WITH CONDITIONAL WRAPPER NULL ON EMPTY NULL ON ERROR) as expected from test_json_dot; +select (test_json_dot.test_json).d[0:] from test_json_dot; +select (test_json_dot.test_json).d[0::int] from test_json_dot; +select (test_json_dot.test_json).d[0::float] from test_json_dot; +select (test_json_dot.test_json).d[0].x[1], json_query(test_json, 'lax $.d[0].x[1]' WITH CONDITIONAL WRAPPER NULL ON EMPTY NULL ON ERROR) as expected from test_json_dot; + +-- complex type with domain over json +create domain json_d as json; +create type comp_json as (a int, b json_d); +create table test_json_domain_dot(a comp_json); +insert into test_json_domain_dot select $$ (1,"{""a"": 3, ""key1"": {""c"": ""42""}, ""key2"": [11, 12]}") $$; +insert into test_json_domain_dot select $$ (1,"{""a"": 3, ""key1"": {""c"": ""42""}, ""key2"": [11, 12, {""x"": [31, 42]}]}") $$; +--object access +select (test_json_domain_dot.a).b.key1.c from test_json_domain_dot; +select (test_json_domain_dot.a).b.key2 from test_json_domain_dot; +select (test_json_domain_dot.a).b.key2[0] from test_json_domain_dot; +select (test_json_domain_dot.a).b.key2[0::text] from test_json_domain_dot; +select (test_json_domain_dot.a).b.key2[2].x[1] from test_json_domain_dot; +-- array access +insert into test_json_domain_dot select $$ (1,"[{""a"": 3}, {""key1"": {""c"": ""42""}}, {""key2"": [11, 12]}]") $$; +select (test_json_domain_dot.a).b[0] from test_json_domain_dot; +select (test_json_domain_dot.a).b[0:] from test_json_domain_dot; +drop table test_json_domain_dot cascade; +drop type comp_json cascade; +drop domain json_d cascade; + +-- nested domains over json +CREATE DOMAIN json_with_name AS json + CHECK ( + -- check that JSON has a "name" field and that it is a string + json_typeof(VALUE->'name') = 'string' + ); +CREATE DOMAIN json_with_name_and_email AS json_with_name + CHECK ( + -- ensure that if "email" exists, it follows a simple email format + VALUE->'email' IS NULL OR (VALUE->>'email' ~ '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$') + ); +CREATE DOMAIN json_user_profile AS json_with_name_and_email + CHECK ( + -- ensure that if "phone" exists, it follows a basic phone format + VALUE->'phone' IS NULL OR (VALUE->>'phone' ~ '^\+\d{1,3}-\d{3}-\d{3}-\d{4}$') + ); +CREATE TABLE json_users (id SERIAL PRIMARY KEY, profile json_user_profile); +INSERT INTO json_users (profile) VALUES ('{"name": "Alice", "email": "[email protected]", "phone": "+1-123-456-7890"}'); +INSERT INTO json_users (profile) VALUES ('{"name": "Bob", "email": "[email protected]", "phone": "+9-876-543-3210", "address": [123, "1st street", "New York", "New York", 12345]}'); +SELECT (json_users.profile).name from json_users; +SELECT (json_users.profile).email from json_users; +SELECT (json_users.profile).phone from json_users; +SELECT (json_users.profile).address from json_users; +SELECT (json_users.profile).address[3] from json_users; + +-- array of nested domains over json +CREATE TABLE json_user_arrs (id SERIAL PRIMARY KEY, profiles json_user_profile[]); +INSERT INTO json_user_arrs (profiles) VALUES (ARRAY['{"name": "Alice", "email": "[email protected]", "phone": "+1-123-456-7890"}'::json_user_profile, '{"name": "Bob", "email": "[email protected]", "phone": "+9-876-543-3210", "address": [123, "1st street", "New York", "New York", 12345]}'::json_user_profile]); +SELECT json_user_arrs.profiles[1] from json_user_arrs; +SELECT json_user_arrs.profiles[2] from json_user_arrs; +SELECT json_user_arrs.profiles[2].address[0] from json_user_arrs; + +drop table json_users; +drop table json_user_arrs; +drop domain json_user_profile; +drop domain json_with_name_and_email; +drop domain json_with_name; diff --git a/src/test/regress/sql/jsonb.sql b/src/test/regress/sql/jsonb.sql index 5f0190d5a2..b6096021bc 100644 --- a/src/test/regress/sql/jsonb.sql +++ b/src/test/regress/sql/jsonb.sql @@ -1559,3 +1559,86 @@ select '12345.0000000000000000000000000000000000000000000005'::jsonb::float8; select '12345.0000000000000000000000000000000000000000000005'::jsonb::int2; select '12345.0000000000000000000000000000000000000000000005'::jsonb::int4; select '12345.0000000000000000000000000000000000000000000005'::jsonb::int8; + +-- simple dot notation +drop table if exists test_jsonb_dot; +create table test_jsonb_dot(id int, test_jsonb jsonb); +insert into test_jsonb_dot select 1, '{"a": 1, "b": 42}'::json; +insert into test_jsonb_dot select 1, '{"a": 2, "b": {"c": 42}}'::json; +insert into test_jsonb_dot select 1, '{"a": 3, "b": {"c": "42"}, "d":[11, 12]}'::json; + +-- member object access +select (test_jsonb_dot.test_jsonb).b from test_jsonb_dot; +select (test_jsonb_dot.test_jsonb).b.c from test_jsonb_dot; +select (test_jsonb_dot.test_jsonb).d from test_jsonb_dot; +select (test_jsonb_dot.test_jsonb)."d" from test_jsonb_dot; +select (test_jsonb_dot.test_jsonb).'d' from test_jsonb_dot; +select (test_jsonb_dot.test_jsonb)['d'] from test_jsonb_dot; + +-- array element access +select (test_jsonb_dot.test_jsonb).d[0] from test_jsonb_dot; +select (test_jsonb_dot.test_jsonb).d[0:] from test_jsonb_dot; +select (test_jsonb_dot.test_jsonb).d[0::int] from test_jsonb_dot; +select (test_jsonb_dot.test_jsonb).d[0::float] from test_jsonb_dot; +select (test_jsonb_dot.test_jsonb).d[0].x[1] from test_jsonb_dot; + +-- wildcard access is not supported +select (test_jsonb_dot.test_jsonb).* from test_jsonb_dot; + +-- complex type with domain over jsonb +create domain jsonb_d as jsonb; +create type comp_jsonb as (a int, b jsonb_d); +create table test_jsonb_domain_dot(a comp_jsonb); +insert into test_jsonb_domain_dot select $$ (1,"{""a"": 3, ""key1"": {""c"": ""42""}, ""key2"": [11, 12]}") $$; +insert into test_jsonb_domain_dot select $$ (1,"{""a"": 3, ""key1"": {""c"": ""42""}, ""key2"": [11, 12, {""x"": [31, 42]}]}") $$; +-- object access +select (test_jsonb_domain_dot.a).b.key1.c from test_jsonb_domain_dot; +select (test_jsonb_domain_dot.a).b.key2 from test_jsonb_domain_dot; +select (test_jsonb_domain_dot.a).b.key2[0] from test_jsonb_domain_dot; +select (test_jsonb_domain_dot.a).b.key2[0::text] from test_jsonb_domain_dot; +select (test_jsonb_domain_dot.a).b.key2[2].x[1] from test_jsonb_domain_dot; +-- array access +insert into test_jsonb_domain_dot select $$ (1,"[{""a"": 3}, {""key1"": {""c"": ""42""}}, {""key2"": [11, 12]}]") $$; +select (test_jsonb_domain_dot.a).b[0] from test_jsonb_domain_dot; +select (test_jsonb_domain_dot.a).b[0:] from test_jsonb_domain_dot; +drop table test_jsonb_domain_dot cascade; +drop type comp_jsonb cascade; +drop domain jsonb_d cascade; + +-- nested domains over jsonb +CREATE DOMAIN jsonb_with_name AS JSONB + CHECK ( + -- check that JSON has a "name" field and that it is a string + VALUE ? 'name' AND jsonb_typeof(VALUE->'name') = 'string' + ); +CREATE DOMAIN jsonb_with_name_and_email AS jsonb_with_name + CHECK ( + -- ensure that if "email" exists, it follows a simple email format + NOT VALUE ? 'email' OR (VALUE->>'email' ~ '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$') + ); +CREATE DOMAIN jsonb_user_profile AS jsonb_with_name_and_email + CHECK ( + -- ensure that if "phone" exists, it follows a basic phone format + NOT VALUE ? 'phone' OR (VALUE->>'phone' ~ '^\+\d{1,3}-\d{3}-\d{3}-\d{4}$') + ); +CREATE TABLE jsonb_users (id SERIAL PRIMARY KEY, profile jsonb_user_profile); +INSERT INTO jsonb_users (profile) VALUES ('{"name": "Alice", "email": "[email protected]", "phone": "+1-123-456-7890"}'); +INSERT INTO jsonb_users (profile) VALUES ('{"name": "Bob", "email": "[email protected]", "phone": "+9-876-543-3210", "address": [123, "1st street", "New York", "New York", 12345]}'); +SELECT (jsonb_users.profile).name from jsonb_users; +SELECT (jsonb_users.profile).email from jsonb_users; +SELECT (jsonb_users.profile).phone from jsonb_users; +SELECT (jsonb_users.profile).address from jsonb_users; +SELECT (jsonb_users.profile).address[3] from jsonb_users; + +-- array of nested domains over jsonb +CREATE TABLE jsonb_user_arrs (id SERIAL PRIMARY KEY, profiles jsonb_user_profile[]); +INSERT INTO jsonb_user_arrs (profiles) VALUES (ARRAY['{"name": "Alice", "email": "[email protected]", "phone": "+1-123-456-7890"}'::jsonb_user_profile, '{"name": "Bob", "email": "[email protected]", "phone": "+9-876-543-3210", "address": [123, "1st street", "New York", "New York", 12345]}'::jsonb_user_profile]); +SELECT jsonb_user_arrs.profiles[1] from jsonb_user_arrs; +SELECT jsonb_user_arrs.profiles[2] from jsonb_user_arrs; +SELECT jsonb_user_arrs.profiles[2].address[0] from jsonb_user_arrs; + +drop table jsonb_users; +drop table jsonb_user_arrs; +drop domain jsonb_user_profile; +drop domain jsonb_with_name_and_email; +drop domain jsonb_with_name; -- 2.39.5 (Apple Git-154) ^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: SQL:2023 JSON simplified accessor support @ 2024-11-14 12:31 Peter Eisentraut <[email protected]> parent: Alexandra Wang <[email protected]> 0 siblings, 1 reply; 8+ messages in thread From: Peter Eisentraut @ 2024-11-14 12:31 UTC (permalink / raw) To: Alexandra Wang <[email protected]>; jian he <[email protected]>; Andrew Dunstan <[email protected]>; +Cc: pgsql-hackers; [email protected] On 07.11.24 22:57, Alexandra Wang wrote: > The v5 patch includes the following updates: > > - Fixed the aforementioned issue and added more tests covering composite > types with domains, nested domains, and arrays of domains over > JSON/JSONB. > > - Refactored the logic for parsing JSON/JSONB object fields by moving it > from ParseFuncOrColumn() to transformIndirection() for improved > readability. The ParseFuncOrColumn() function is already handling both > single-argument function calls and composite types, and it has other > callers besides transformIndirection(). This patch implements array subscripting support for the json type, but it does it in a non-standard way, using ParseJsonSimplifiedAccessorArrayElement(). This would be better done by providing a typsubscript function for the json type. This is what jsonb already has, which is why your patch doesn't need to provide the array support for jsonb. I suggest you implement the typsubscript support for the json type (make it a separate patch but you can keep it in this thread IMO) and remove the custom code from this patch. A few comments on the tests: The tests look good to me. Good coverage of weirdly nested types. Results look correct. +drop table if exists test_json_dot; This can be omitted, since we know that the table doesn't exist yet. This code could be written in the more conventional insert ... values syntax: +insert into test_json_dot select 1, '{"a": 1, "b": 42}'::json; +insert into test_json_dot select 1, '{"a": 2, "b": {"c": 42}}'::json; +insert into test_json_dot select 1, '{"a": 3, "b": {"c": "42"}, "d":[11, 12]}'::json; +insert into test_json_dot select 1, '{"a": 3, "b": {"c": "42"}, "d":[{"x": [11, 12]}, {"y": [21, 22]}]}'::json; Then the ::json casts can also go away. Also, using a different value for "id" for each row would be more useful, so that the subsequent tests could then be written like select id, (test_jsonb_dot.test_jsonb).b from test_jsonb_dot; so we can see which result corresponds to which input row. Also make id the primary key in this table. Also, let's keep the json and the jsonb variants aligned. There are some small differences, like the test_json_dot table having 4 rows but the test_jsonb_dot having 3 rows. And the array and wildcard tests in the opposite order. Not a big deal, but keeping these the same helps eyeballing the test files. Maybe add a comment somewhere in this file that you are running the json_query equivalents to cross-check the semantics of the dot syntax. Some documentation should be written. This looks like this right place to start: https://www.postgresql.org/docs/devel/sql-expressions.html#FIELD-SELECTION and them maybe some cross-linking between there and the sections on JSON types and operators. ^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: SQL:2023 JSON simplified accessor support @ 2024-11-20 00:06 Nikita Glukhov <[email protected]> parent: Peter Eisentraut <[email protected]> 0 siblings, 0 replies; 8+ messages in thread From: Nikita Glukhov @ 2024-11-20 00:06 UTC (permalink / raw) To: Alexandra Wang <[email protected]>; Peter Eisentraut <[email protected]>; +Cc: pgsql-hackers; Andrew Dunstan <[email protected]>; David E. Wheeler <[email protected]>; jian he <[email protected]> Hi, hackers. I have implemented dot notation for jsonb using type subscripting back in April 2023, but failed post it because I left Postgres Professional company soon after and have not worked anywhere since, not even had any interest in programming. But yesterday I accidentally decided to look what is going on at commitfests and found this thread. I immediately started to rebase code from PG16, fixed some bugs, and now I'm ready to present my version of the patches which is much more complex. Unfortunately, I probably won't be able to devote that much time to the patches as before. Description of the patches: 1. Allow transformation only of a sublist of subscripts This gives ability to custom subscripting code to consume only the supported prefix of the subscripts list. Remaining subscripts are applied to the result of subscription which can be of different data type. Example of behavior change for hstore which always consumes only the first subscript and returns text: =# select (hstore 'foo=>bar')['foo']['bar']; -ERROR: hstore allows only one subscript +ERROR: cannot subscript type text because it does not support subscripting 2. GUC compat_field_notation disables treating of field selection as a function application (non-standard PG feature) if the column data type supports subscripting. Example for function hstore_hash(hstore) (after patch #4 applied): =# set compat_field_notation = on; =# select (hstore 'foo=>bar,hstore_hash=>123').hstore_hash; -- function hstore_hash ------------ -1718799972 =# select (hstore 'foo=>bar,hstore_hash=>123').foo; foo ----- bar =# set compat_field_notation = off; -- default =# select (hstore 'foo=>bar').hstore_hash; -- missing key hstore_hash ------------- =# select (hstore 'foo=>bar,hstore_hash=>123').hstore_hash; hstore_hash ------------- 123 3. Enable processing of field accessors by generic subscripting code. Field accessors are represented as String nodes in refupperexprs for distinguishing from ordinary text subscripts which can be needed for correct EXPLAIN. Strings node seem to no longer be a valid expression nodes, so I had to add quite ugly handling for them in nodeFuncs etc during rebase to PG18. 4. Implement read-only dot notation for hstore (see example above). 5. Enable processing of .* by generic subscripting (similary to #3) 6. Export jsonPathFromParseResult(): simple refactoring for #7. 7. Implement read-only dot notation for jsonb using jsonpath. A list of subscripts is converted to jsonpath using JsonPathParseItem. Non-constant array accessors are replaced with jsonpath parameters. TODO: - wildcard array accessor [*] - item methods There is some inconsistency in subscripting in jsonpath, which supports only arrays with numeric indexes, and existing generic subscripting for jsonb, which also supports indexing of objects by string keys. -- JSON_QUERY(jb, '$.a["b"]') =# select (jsonb '{"a": {"b": 1}}').a['b']; a --- NULL -- JSON_QUERY(jb, '$.a')['b'] = generic subscripting after JSON_QUERY =# select ((jsonb '{"a": {"b": 1}}').a)['b']; a --- 1 So, I think it would be good to enable subscripting of objects by strings in jsonpath. 8. Extract transformColumnRefInternal(): simple refactoring for #9 9. Enable non-parenthesized column references in dot notation. I think such patch needs a separate thread. This feature, required by the SQL standard, is implemented by calling transformColumnRefInternal() in a loop and passing different prefixes of the field chain. But I'm not sure how correct this is, I simply tried to preserve compatibility with existing name resolution rules. For example, for A.B.C.D.E we will try the following variants of splitting to column reference and indirections: (A.B.C.D).E = db A, schema B, table C, column D (A.B.C).D.E = schema A, table B, column C (A.B).C.D.E = table A, column B (A).B.C.D.E is not tried because by the SQL standard column reference in dot notation should be prefixed with table name. Although it would be very nice to write "jb.key" instead of "tab.jb.key". There is also an inconsistency in execution when the accessor chain is separated by parentheses. Example: (jb).a [0] => JSON_QUERY(jb, '$.a[0]') ((jb).a)[0] => JSON_QUERY(jb, '$.a') [0] =# select (jsonb '[{"a": 1}, {"a": 2}]').a[0]; a -------- [1, 2] - jsonpath '$.a' returns two items: 1, 2 - jsonpath '[0]' returns the same items due to auto array wrapping - items wrapped into array =# select ((jsonb '[{"a": 1}, {"a": 2}]').a)[0]; a --- 1 - JSON_QUERY(jb, '$.a') returns array [1, 2], - generic subscript '[0]' extracts its first element The problem could be solved if we somehow allowed intermediate jsonpaths to return unwrapped items sequences (like SRF). Also I think it would be an interesting task to implement the assignment to JSON using dot notation, but it is unclear what to do here with .* and [*]. Attachments: [text/x-patch] v6-0001-Allow-transformation-only-of-a-sublist-of-subscri.patch (8.4K, ../../[email protected]/3-v6-0001-Allow-transformation-only-of-a-sublist-of-subscri.patch) download | inline diff: From d4df1c1db6d64651bb0f7358561ca84231a55090 Mon Sep 17 00:00:00 2001 From: Nikita Glukhov <[email protected]> Date: Fri, 14 Oct 2022 15:35:22 +0300 Subject: [PATCH v6 01/11] Allow transformation only of a sublist of subscripts --- contrib/hstore/expected/hstore.out | 4 +++- contrib/hstore/hstore_subs.c | 11 +++++++---- src/backend/parser/parse_expr.c | 9 ++++----- src/backend/parser/parse_node.c | 4 ++-- src/backend/parser/parse_target.c | 5 ++++- src/backend/utils/adt/arraysubs.c | 5 +++-- src/backend/utils/adt/jsonbsubs.c | 6 ++++-- src/include/nodes/subscripting.h | 2 +- src/include/parser/parse_node.h | 2 +- 9 files changed, 29 insertions(+), 19 deletions(-) diff --git a/contrib/hstore/expected/hstore.out b/contrib/hstore/expected/hstore.out index 1836c9acf39..9e2421750fb 100644 --- a/contrib/hstore/expected/hstore.out +++ b/contrib/hstore/expected/hstore.out @@ -1605,7 +1605,9 @@ select f2['d'], f2['x'] is null as x_isnull from test_json_agg; (3 rows) select f2['d']['e'] from test_json_agg; -- error -ERROR: hstore allows only one subscript +ERROR: cannot subscript type text because it does not support subscripting +LINE 1: select f2['d']['e'] from test_json_agg; + ^ select f2['d':'e'] from test_json_agg; -- error ERROR: hstore allows only one subscript update test_json_agg set f2['d'] = f2['e'], f2['x'] = 'xyzzy'; diff --git a/contrib/hstore/hstore_subs.c b/contrib/hstore/hstore_subs.c index 1443d8634b2..efdd0f22ac1 100644 --- a/contrib/hstore/hstore_subs.c +++ b/contrib/hstore/hstore_subs.c @@ -40,7 +40,7 @@ */ static void hstore_subscript_transform(SubscriptingRef *sbsref, - List *indirection, + List **indirection, ParseState *pstate, bool isSlice, bool isAssignment) @@ -49,17 +49,20 @@ hstore_subscript_transform(SubscriptingRef *sbsref, Node *subexpr; /* We support only single-subscript, non-slice cases */ - if (isSlice || list_length(indirection) != 1) + if (isSlice || list_length(*indirection) < 1) ereport(ERROR, (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), errmsg("hstore allows only one subscript"), parser_errposition(pstate, - exprLocation((Node *) indirection)))); + exprLocation((Node *) *indirection)))); /* Transform the subscript expression to type text */ - ai = linitial_node(A_Indices, indirection); + ai = linitial_node(A_Indices, *indirection); Assert(ai->uidx != NULL && ai->lidx == NULL && !ai->is_slice); + /* hstore allows only one subscript */ + *indirection = list_delete_first(*indirection); + subexpr = transformExpr(pstate, ai->uidx, pstate->p_expr_kind); /* If it's not text already, try to coerce */ subexpr = coerce_to_target_type(pstate, diff --git a/src/backend/parser/parse_expr.c b/src/backend/parser/parse_expr.c index c2806297aa4..7d57bde8134 100644 --- a/src/backend/parser/parse_expr.c +++ b/src/backend/parser/parse_expr.c @@ -466,14 +466,13 @@ transformIndirection(ParseState *pstate, A_Indirection *ind) Assert(IsA(n, String)); /* process subscripts before this field selection */ - if (subscripts) + while (subscripts) result = (Node *) transformContainerSubscripts(pstate, result, exprType(result), exprTypmod(result), - subscripts, + &subscripts, false); - subscripts = NIL; newresult = ParseFuncOrColumn(pstate, list_make1(n), @@ -488,12 +487,12 @@ transformIndirection(ParseState *pstate, A_Indirection *ind) } } /* process trailing subscripts, if any */ - if (subscripts) + while (subscripts) result = (Node *) transformContainerSubscripts(pstate, result, exprType(result), exprTypmod(result), - subscripts, + &subscripts, false); return result; diff --git a/src/backend/parser/parse_node.c b/src/backend/parser/parse_node.c index 9361b5252d8..a24ae783b5f 100644 --- a/src/backend/parser/parse_node.c +++ b/src/backend/parser/parse_node.c @@ -244,7 +244,7 @@ transformContainerSubscripts(ParseState *pstate, Node *containerBase, Oid containerType, int32 containerTypMod, - List *indirection, + List **indirection, bool isAssignment) { SubscriptingRef *sbsref; @@ -280,7 +280,7 @@ transformContainerSubscripts(ParseState *pstate, * element. If any of the items are slice specifiers (lower:upper), then * the subscript expression means a container slice operation. */ - foreach(idx, indirection) + foreach(idx, *indirection) { A_Indices *ai = lfirst_node(A_Indices, idx); diff --git a/src/backend/parser/parse_target.c b/src/backend/parser/parse_target.c index 76bf88c3ca2..9bed545eb00 100644 --- a/src/backend/parser/parse_target.c +++ b/src/backend/parser/parse_target.c @@ -936,9 +936,12 @@ transformAssignmentSubscripts(ParseState *pstate, basenode, containerType, containerTypMod, - subscripts, + &subscripts, true); + if (subscripts) + elog(ERROR, "subscripting assignment is not supported"); + typeNeeded = sbsref->refrestype; typmodNeeded = sbsref->reftypmod; diff --git a/src/backend/utils/adt/arraysubs.c b/src/backend/utils/adt/arraysubs.c index 6f68dfa5b23..d590fdb6958 100644 --- a/src/backend/utils/adt/arraysubs.c +++ b/src/backend/utils/adt/arraysubs.c @@ -53,7 +53,7 @@ typedef struct ArraySubWorkspace */ static void array_subscript_transform(SubscriptingRef *sbsref, - List *indirection, + List **indirection, ParseState *pstate, bool isSlice, bool isAssignment) @@ -70,7 +70,7 @@ array_subscript_transform(SubscriptingRef *sbsref, * indirection items to slices by treating the single subscript as the * upper bound and supplying an assumed lower bound of 1. */ - foreach(idx, indirection) + foreach(idx, *indirection) { A_Indices *ai = lfirst_node(A_Indices, idx); Node *subexpr; @@ -151,6 +151,7 @@ array_subscript_transform(SubscriptingRef *sbsref, list_length(upperIndexpr), MAXDIM))); /* We need not check lowerIndexpr separately */ + *indirection = NIL; /* * Determine the result type of the subscripting operation. It's the same * as the array type if we're slicing, else it's the element type. In diff --git a/src/backend/utils/adt/jsonbsubs.c b/src/backend/utils/adt/jsonbsubs.c index 2b037131c91..d37ee004e16 100644 --- a/src/backend/utils/adt/jsonbsubs.c +++ b/src/backend/utils/adt/jsonbsubs.c @@ -41,7 +41,7 @@ typedef struct JsonbSubWorkspace */ static void jsonb_subscript_transform(SubscriptingRef *sbsref, - List *indirection, + List **indirection, ParseState *pstate, bool isSlice, bool isAssignment) @@ -53,7 +53,7 @@ jsonb_subscript_transform(SubscriptingRef *sbsref, * Transform and convert the subscript expressions. Jsonb subscripting * does not support slices, look only and the upper index. */ - foreach(idx, indirection) + foreach(idx, *indirection) { A_Indices *ai = lfirst_node(A_Indices, idx); Node *subExpr; @@ -159,6 +159,8 @@ jsonb_subscript_transform(SubscriptingRef *sbsref, /* Determine the result type of the subscripting operation; always jsonb */ sbsref->refrestype = JSONBOID; sbsref->reftypmod = -1; + + *indirection = NIL; } /* diff --git a/src/include/nodes/subscripting.h b/src/include/nodes/subscripting.h index 597209ba42d..11a8ea9c1e0 100644 --- a/src/include/nodes/subscripting.h +++ b/src/include/nodes/subscripting.h @@ -93,7 +93,7 @@ struct SubscriptExecSteps; * assignment must return. */ typedef void (*SubscriptTransform) (SubscriptingRef *sbsref, - List *indirection, + List **indirection, struct ParseState *pstate, bool isSlice, bool isAssignment); diff --git a/src/include/parser/parse_node.h b/src/include/parser/parse_node.h index 2375e95c107..195bd4dccb8 100644 --- a/src/include/parser/parse_node.h +++ b/src/include/parser/parse_node.h @@ -370,7 +370,7 @@ extern SubscriptingRef *transformContainerSubscripts(ParseState *pstate, Node *containerBase, Oid containerType, int32 containerTypMod, - List *indirection, + List **indirection, bool isAssignment); extern Const *make_const(ParseState *pstate, A_Const *aconst); -- 2.25.1 [text/x-patch] v6-0002-Add-GUC-compat_field_notation.patch (1.8K, ../../[email protected]/4-v6-0002-Add-GUC-compat_field_notation.patch) download | inline diff: From 505260eae8086b6babbdee9b4eafc95f728bd1cd Mon Sep 17 00:00:00 2001 From: Nikita Glukhov <[email protected]> Date: Sat, 1 Apr 2023 16:19:51 +0300 Subject: [PATCH v6 02/11] Add GUC compat_field_notation --- src/backend/parser/parse_expr.c | 1 + src/backend/utils/misc/guc_tables.c | 11 +++++++++++ src/include/parser/parse_expr.h | 1 + 3 files changed, 13 insertions(+) diff --git a/src/backend/parser/parse_expr.c b/src/backend/parser/parse_expr.c index 7d57bde8134..1c8c4149798 100644 --- a/src/backend/parser/parse_expr.c +++ b/src/backend/parser/parse_expr.c @@ -42,6 +42,7 @@ /* GUC parameters */ bool Transform_null_equals = false; +bool compat_field_notation = false; static Node *transformExprRecurse(ParseState *pstate, Node *expr); diff --git a/src/backend/utils/misc/guc_tables.c b/src/backend/utils/misc/guc_tables.c index 8a67f01200c..06294eda1f6 100644 --- a/src/backend/utils/misc/guc_tables.c +++ b/src/backend/utils/misc/guc_tables.c @@ -2076,6 +2076,17 @@ struct config_bool ConfigureNamesBool[] = NULL, NULL, NULL }, + { + {"compat_field_notation", PGC_USERSET, COMPAT_OPTIONS_PREVIOUS, + gettext_noop("Give a preference to function field notation over generic type subscripting."), + NULL, + GUC_EXPLAIN + }, + &compat_field_notation, + false, + NULL, NULL, NULL + }, + /* End-of-list marker */ { {NULL, 0, 0, NULL, NULL}, NULL, false, NULL, NULL, NULL diff --git a/src/include/parser/parse_expr.h b/src/include/parser/parse_expr.h index 9b46dfd9ecc..f1f479fa56b 100644 --- a/src/include/parser/parse_expr.h +++ b/src/include/parser/parse_expr.h @@ -17,6 +17,7 @@ /* GUC parameters */ extern PGDLLIMPORT bool Transform_null_equals; +extern PGDLLIMPORT bool compat_field_notation; extern Node *transformExpr(ParseState *pstate, Node *expr, ParseExprKind exprKind); -- 2.25.1 [text/x-patch] v6-0003-Pass-field-accessors-to-generic-subscripting.patch (18.9K, ../../[email protected]/5-v6-0003-Pass-field-accessors-to-generic-subscripting.patch) download | inline diff: From 6ce3b92d617b183d7577dc4b630a6ef993afc3cd Mon Sep 17 00:00:00 2001 From: Nikita Glukhov <[email protected]> Date: Sat, 1 Apr 2023 16:21:20 +0300 Subject: [PATCH v6 03/11] Pass field accessors to generic subscripting --- contrib/hstore/hstore_subs.c | 43 +++++++------ src/backend/executor/execExpr.c | 24 ++++++-- src/backend/nodes/nodeFuncs.c | 73 ++++++++++++++++++++--- src/backend/parser/parse_collate.c | 22 +++++-- src/backend/parser/parse_expr.c | 96 +++++++++++++++++++++++------- src/backend/parser/parse_node.c | 39 +++++++++++- src/backend/parser/parse_target.c | 3 +- src/backend/utils/adt/arraysubs.c | 14 ++++- src/backend/utils/adt/jsonbsubs.c | 11 +++- src/backend/utils/adt/ruleutils.c | 29 ++++++--- src/include/parser/parse_node.h | 3 +- 11 files changed, 282 insertions(+), 75 deletions(-) diff --git a/contrib/hstore/hstore_subs.c b/contrib/hstore/hstore_subs.c index efdd0f22ac1..304cee8c024 100644 --- a/contrib/hstore/hstore_subs.c +++ b/contrib/hstore/hstore_subs.c @@ -45,7 +45,7 @@ hstore_subscript_transform(SubscriptingRef *sbsref, bool isSlice, bool isAssignment) { - A_Indices *ai; + Node *ind = linitial(*indirection); Node *subexpr; /* We support only single-subscript, non-slice cases */ @@ -56,27 +56,34 @@ hstore_subscript_transform(SubscriptingRef *sbsref, parser_errposition(pstate, exprLocation((Node *) *indirection)))); - /* Transform the subscript expression to type text */ - ai = linitial_node(A_Indices, *indirection); - Assert(ai->uidx != NULL && ai->lidx == NULL && !ai->is_slice); + if (IsA(ind, A_Indices)) + { + A_Indices *ai = castNode(A_Indices, ind); + + /* Transform the subscript expression to type text */ + ai = linitial_node(A_Indices, *indirection); + Assert(ai->uidx != NULL && ai->lidx == NULL && !ai->is_slice); + + subexpr = transformExpr(pstate, ai->uidx, pstate->p_expr_kind); + /* If it's not text already, try to coerce */ + subexpr = coerce_to_target_type(pstate, + subexpr, exprType(subexpr), + TEXTOID, -1, + COERCION_ASSIGNMENT, + COERCE_IMPLICIT_CAST, + -1); + if (subexpr == NULL) + ereport(ERROR, + (errcode(ERRCODE_DATATYPE_MISMATCH), + errmsg("hstore subscript must have type text"), + parser_errposition(pstate, exprLocation(ai->uidx)))); + } + else + return; /* hstore allows only one subscript */ *indirection = list_delete_first(*indirection); - subexpr = transformExpr(pstate, ai->uidx, pstate->p_expr_kind); - /* If it's not text already, try to coerce */ - subexpr = coerce_to_target_type(pstate, - subexpr, exprType(subexpr), - TEXTOID, -1, - COERCION_ASSIGNMENT, - COERCE_IMPLICIT_CAST, - -1); - if (subexpr == NULL) - ereport(ERROR, - (errcode(ERRCODE_DATATYPE_MISMATCH), - errmsg("hstore subscript must have type text"), - parser_errposition(pstate, exprLocation(ai->uidx)))); - /* ... and store the transformed subscript into the SubscriptRef node */ sbsref->refupperindexpr = list_make1(subexpr); sbsref->reflowerindexpr = NIL; diff --git a/src/backend/executor/execExpr.c b/src/backend/executor/execExpr.c index 8f7a5340059..a6656b7fac6 100644 --- a/src/backend/executor/execExpr.c +++ b/src/backend/executor/execExpr.c @@ -3189,9 +3189,15 @@ ExecInitSubscriptingRef(ExprEvalStep *scratch, SubscriptingRef *sbsref, { sbsrefstate->upperprovided[i] = true; /* Each subscript is evaluated into appropriate array entry */ - ExecInitExprRec(e, state, - &sbsrefstate->upperindex[i], - &sbsrefstate->upperindexnull[i]); + if (IsA(e, String)) + { + sbsrefstate->upperindex[i] = CStringGetTextDatum(strVal(e)); + sbsrefstate->upperindexnull[i] = false; + } + else + ExecInitExprRec(e, state, + &sbsrefstate->upperindex[i], + &sbsrefstate->upperindexnull[i]); } i++; } @@ -3212,9 +3218,15 @@ ExecInitSubscriptingRef(ExprEvalStep *scratch, SubscriptingRef *sbsref, { sbsrefstate->lowerprovided[i] = true; /* Each subscript is evaluated into appropriate array entry */ - ExecInitExprRec(e, state, - &sbsrefstate->lowerindex[i], - &sbsrefstate->lowerindexnull[i]); + if (IsA(e, String)) + { + sbsrefstate->lowerindex[i] = CStringGetTextDatum(strVal(e)); + sbsrefstate->lowerindexnull[i] = false; + } + else + ExecInitExprRec(e, state, + &sbsrefstate->lowerindex[i], + &sbsrefstate->lowerindexnull[i]); } i++; } diff --git a/src/backend/nodes/nodeFuncs.c b/src/backend/nodes/nodeFuncs.c index 3060847b133..2baf7702ee6 100644 --- a/src/backend/nodes/nodeFuncs.c +++ b/src/backend/nodes/nodeFuncs.c @@ -2171,12 +2171,28 @@ expression_tree_walker_impl(Node *node, case T_SubscriptingRef: { SubscriptingRef *sbsref = (SubscriptingRef *) node; + ListCell *lc; + + /* + * Recurse directly for upper/lower container index lists, + * skipping String subscripts used for dot notation. + */ + foreach(lc, sbsref->refupperindexpr) + { + Node *expr = lfirst(lc); + + if (expr && !IsA(expr, String) && WALK(expr)) + return true; + } + + foreach(lc, sbsref->reflowerindexpr) + { + Node *expr = lfirst(lc); + + if (expr && !IsA(expr, String) && WALK(expr)) + return true; + } - /* recurse directly for upper/lower container index lists */ - if (LIST_WALK(sbsref->refupperindexpr)) - return true; - if (LIST_WALK(sbsref->reflowerindexpr)) - return true; /* walker must see the refexpr and refassgnexpr, however */ if (WALK(sbsref->refexpr)) return true; @@ -3069,12 +3085,51 @@ expression_tree_mutator_impl(Node *node, { SubscriptingRef *sbsref = (SubscriptingRef *) node; SubscriptingRef *newnode; + ListCell *lc; + List *exprs = NIL; FLATCOPY(newnode, sbsref, SubscriptingRef); - MUTATE(newnode->refupperindexpr, sbsref->refupperindexpr, - List *); - MUTATE(newnode->reflowerindexpr, sbsref->reflowerindexpr, - List *); + + foreach(lc, sbsref->refupperindexpr) + { + Node *expr = lfirst(lc); + + if (expr && IsA(expr, String)) + { + String *str; + + FLATCOPY(str, expr, String); + expr = (Node *) str; + } + else + expr = mutator(expr, context); + + exprs = lappend(exprs, expr); + } + + newnode->refupperindexpr = exprs; + + exprs = NIL; + + foreach(lc, sbsref->reflowerindexpr) + { + Node *expr = lfirst(lc); + + if (expr && IsA(expr, String)) + { + String *str; + + FLATCOPY(str, expr, String); + expr = (Node *) str; + } + else + expr = mutator(expr, context); + + exprs = lappend(exprs, expr); + } + + newnode->reflowerindexpr = exprs; + MUTATE(newnode->refexpr, sbsref->refexpr, Expr *); MUTATE(newnode->refassgnexpr, sbsref->refassgnexpr, diff --git a/src/backend/parser/parse_collate.c b/src/backend/parser/parse_collate.c index 44529bb49e6..be837fff349 100644 --- a/src/backend/parser/parse_collate.c +++ b/src/backend/parser/parse_collate.c @@ -680,11 +680,25 @@ assign_collations_walker(Node *node, assign_collations_context *context) * contribute anything.) */ SubscriptingRef *sbsref = (SubscriptingRef *) node; + ListCell *lc; + + /* skip String subscripts used for dot notation */ + foreach(lc, sbsref->refupperindexpr) + { + Node *expr = lfirst(lc); + + if (expr && !IsA(expr, String)) + assign_expr_collations(context->pstate, expr); + } + + foreach(lc, sbsref->reflowerindexpr) + { + Node *expr = lfirst(lc); + + if (expr && !IsA(expr, String)) + assign_expr_collations(context->pstate, expr); + } - assign_expr_collations(context->pstate, - (Node *) sbsref->refupperindexpr); - assign_expr_collations(context->pstate, - (Node *) sbsref->reflowerindexpr); (void) assign_collations_walker((Node *) sbsref->refexpr, &loccontext); (void) assign_collations_walker((Node *) sbsref->refassgnexpr, diff --git a/src/backend/parser/parse_expr.c b/src/backend/parser/parse_expr.c index 1c8c4149798..735d5c88729 100644 --- a/src/backend/parser/parse_expr.c +++ b/src/backend/parser/parse_expr.c @@ -462,19 +462,78 @@ transformIndirection(ParseState *pstate, A_Indirection *ind) } else { - Node *newresult; - Assert(IsA(n, String)); - /* process subscripts before this field selection */ - while (subscripts) - result = (Node *) transformContainerSubscripts(pstate, - result, - exprType(result), - exprTypmod(result), - &subscripts, - false); + if (compat_field_notation) + { + /* try to process subscripts before this field selection */ + List *newsubscripts = list_copy(subscripts); + Node *newresult = result; + + while (newsubscripts) + newresult = (Node *) + transformContainerSubscripts(pstate, + newresult, + exprType(newresult), + exprTypmod(newresult), + &newsubscripts, + false, + false); + + newresult = ParseFuncOrColumn(pstate, + list_make1(n), + list_make1(newresult), + last_srf, + NULL, + false, + location); + if (newresult != NULL) + { + result = newresult; + subscripts = newsubscripts; + continue; + } + + /* + * Failed to consume field select, add it to the list + * for trying generic subscripting later. + */ + } + subscripts = lappend(subscripts, n); + } + } + + /* process trailing subscripts, if any */ + while (subscripts) + { + Node *newresult = (Node *) + transformContainerSubscripts(pstate, + result, + exprType(result), + exprTypmod(result), + &subscripts, + false, + !compat_field_notation); + + if (!newresult) + { + /* generic subscripting failed */ + Node *n; + + Assert(!compat_field_notation); + Assert(subscripts); + + n = linitial(subscripts); + + if (!IsA(n, String)) + ereport(ERROR, + (errcode(ERRCODE_DATATYPE_MISMATCH), + errmsg("cannot subscript type %s because it does not support subscripting", + format_type_be(exprType(result))), + parser_errposition(pstate, exprLocation(result)))); + + /* try to find function for field selection */ newresult = ParseFuncOrColumn(pstate, list_make1(n), list_make1(result), @@ -482,19 +541,16 @@ transformIndirection(ParseState *pstate, A_Indirection *ind) NULL, false, location); - if (newresult == NULL) + + if (!newresult) unknown_attribute(pstate, result, strVal(n), location); - result = newresult; + + /* consume field select */ + subscripts = list_delete_first(subscripts); } + + result = newresult; } - /* process trailing subscripts, if any */ - while (subscripts) - result = (Node *) transformContainerSubscripts(pstate, - result, - exprType(result), - exprTypmod(result), - &subscripts, - false); return result; } diff --git a/src/backend/parser/parse_node.c b/src/backend/parser/parse_node.c index a24ae783b5f..3fe0d454eb6 100644 --- a/src/backend/parser/parse_node.c +++ b/src/backend/parser/parse_node.c @@ -245,13 +245,15 @@ transformContainerSubscripts(ParseState *pstate, Oid containerType, int32 containerTypMod, List **indirection, - bool isAssignment) + bool isAssignment, + bool noError) { SubscriptingRef *sbsref; const SubscriptRoutines *sbsroutines; Oid elementType; bool isSlice = false; ListCell *idx; + int indirection_length = list_length(*indirection); /* * Determine the actual container type, smashing any domain. In the @@ -267,11 +269,16 @@ transformContainerSubscripts(ParseState *pstate, */ sbsroutines = getSubscriptingRoutines(containerType, &elementType); if (!sbsroutines) + { + if (noError) + return NULL; + ereport(ERROR, (errcode(ERRCODE_DATATYPE_MISMATCH), errmsg("cannot subscript type %s because it does not support subscripting", format_type_be(containerType)), parser_errposition(pstate, exprLocation(containerBase)))); + } /* * Detect whether any of the indirection items are slice specifiers. @@ -282,9 +289,9 @@ transformContainerSubscripts(ParseState *pstate, */ foreach(idx, *indirection) { - A_Indices *ai = lfirst_node(A_Indices, idx); + Node *ai = lfirst(idx); - if (ai->is_slice) + if (IsA(ai, A_Indices) && castNode(A_Indices, ai)->is_slice) { isSlice = true; break; @@ -312,6 +319,32 @@ transformContainerSubscripts(ParseState *pstate, sbsroutines->transform(sbsref, indirection, pstate, isSlice, isAssignment); + /* + * Error out, if datatyoe falied to consume any indirection elements. + */ + if (list_length(*indirection) == indirection_length) + { + Node *ind = linitial(*indirection); + + if (noError) + return NULL; + + if (IsA(ind, String)) + ereport(ERROR, + (errcode(ERRCODE_DATATYPE_MISMATCH), + errmsg("type %s does not support column notation", + format_type_be(containerType)), + parser_errposition(pstate, exprLocation(containerBase)))); + else if (IsA(ind, A_Indices)) + ereport(ERROR, + (errcode(ERRCODE_DATATYPE_MISMATCH), + errmsg("type %s does not support array subscripting", + format_type_be(containerType)), + parser_errposition(pstate, exprLocation(containerBase)))); + else + elog(ERROR, "invalid indirection operation: %d", nodeTag(ind)); + } + /* * Verify we got a valid type (this defends, for example, against someone * using array_subscript_handler as typsubscript without setting typelem). diff --git a/src/backend/parser/parse_target.c b/src/backend/parser/parse_target.c index 9bed545eb00..2dbcfd7488b 100644 --- a/src/backend/parser/parse_target.c +++ b/src/backend/parser/parse_target.c @@ -937,7 +937,8 @@ transformAssignmentSubscripts(ParseState *pstate, containerType, containerTypMod, &subscripts, - true); + true, + false); if (subscripts) elog(ERROR, "subscripting assignment is not supported"); diff --git a/src/backend/utils/adt/arraysubs.c b/src/backend/utils/adt/arraysubs.c index d590fdb6958..ab19edd5bf6 100644 --- a/src/backend/utils/adt/arraysubs.c +++ b/src/backend/utils/adt/arraysubs.c @@ -61,6 +61,7 @@ array_subscript_transform(SubscriptingRef *sbsref, List *upperIndexpr = NIL; List *lowerIndexpr = NIL; ListCell *idx; + int ndim; /* * Transform the subscript expressions, and separate upper and lower @@ -72,9 +73,14 @@ array_subscript_transform(SubscriptingRef *sbsref, */ foreach(idx, *indirection) { - A_Indices *ai = lfirst_node(A_Indices, idx); + A_Indices *ai; Node *subexpr; + if (!IsA(lfirst(idx), A_Indices)) + break; + + ai = lfirst_node(A_Indices, idx); + if (isSlice) { if (ai->lidx) @@ -144,14 +150,16 @@ array_subscript_transform(SubscriptingRef *sbsref, sbsref->reflowerindexpr = lowerIndexpr; /* Verify subscript list lengths are within implementation limit */ - if (list_length(upperIndexpr) > MAXDIM) + ndim = list_length(upperIndexpr); + if (ndim > MAXDIM) ereport(ERROR, (errcode(ERRCODE_PROGRAM_LIMIT_EXCEEDED), errmsg("number of array dimensions (%d) exceeds the maximum allowed (%d)", list_length(upperIndexpr), MAXDIM))); /* We need not check lowerIndexpr separately */ - *indirection = NIL; + *indirection = list_delete_first_n(*indirection, ndim); + /* * Determine the result type of the subscripting operation. It's the same * as the array type if we're slicing, else it's the element type. In diff --git a/src/backend/utils/adt/jsonbsubs.c b/src/backend/utils/adt/jsonbsubs.c index d37ee004e16..6ecd320bc52 100644 --- a/src/backend/utils/adt/jsonbsubs.c +++ b/src/backend/utils/adt/jsonbsubs.c @@ -55,9 +55,14 @@ jsonb_subscript_transform(SubscriptingRef *sbsref, */ foreach(idx, *indirection) { - A_Indices *ai = lfirst_node(A_Indices, idx); + A_Indices *ai; Node *subExpr; + if (!IsA(lfirst(idx), A_Indices)) + break; + + ai = lfirst_node(A_Indices, idx); + if (isSlice) { Node *expr = ai->uidx ? ai->uidx : ai->lidx; @@ -160,7 +165,9 @@ jsonb_subscript_transform(SubscriptingRef *sbsref, sbsref->refrestype = JSONBOID; sbsref->reftypmod = -1; - *indirection = NIL; + /* Remove processed elements */ + if (upperIndexpr) + *indirection = list_delete_first_n(*indirection, list_length(upperIndexpr)); } /* diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c index a39068d1bf1..b0b25d8b9e5 100644 --- a/src/backend/utils/adt/ruleutils.c +++ b/src/backend/utils/adt/ruleutils.c @@ -47,6 +47,7 @@ #include "nodes/makefuncs.h" #include "nodes/nodeFuncs.h" #include "nodes/pathnodes.h" +#include "nodes/subscripting.h" #include "optimizer/optimizer.h" #include "parser/parse_agg.h" #include "parser/parse_func.h" @@ -12838,17 +12839,29 @@ printSubscripts(SubscriptingRef *sbsref, deparse_context *context) lowlist_item = list_head(sbsref->reflowerindexpr); /* could be NULL */ foreach(uplist_item, sbsref->refupperindexpr) { - appendStringInfoChar(buf, '['); - if (lowlist_item) + Node *up = (Node *) lfirst(uplist_item); + + if (IsA(up, String)) + { + appendStringInfoChar(buf, '.'); + appendStringInfoString(buf, quote_identifier(strVal(up))); + } + else { + appendStringInfoChar(buf, '['); + if (lowlist_item) + { + /* If subexpression is NULL, get_rule_expr prints nothing */ + get_rule_expr((Node *) lfirst(lowlist_item), context, false); + appendStringInfoChar(buf, ':'); + } /* If subexpression is NULL, get_rule_expr prints nothing */ - get_rule_expr((Node *) lfirst(lowlist_item), context, false); - appendStringInfoChar(buf, ':'); - lowlist_item = lnext(sbsref->reflowerindexpr, lowlist_item); + get_rule_expr((Node *) lfirst(uplist_item), context, false); + appendStringInfoChar(buf, ']'); } - /* If subexpression is NULL, get_rule_expr prints nothing */ - get_rule_expr((Node *) lfirst(uplist_item), context, false); - appendStringInfoChar(buf, ']'); + + if (lowlist_item) + lowlist_item = lnext(sbsref->reflowerindexpr, lowlist_item); } } diff --git a/src/include/parser/parse_node.h b/src/include/parser/parse_node.h index 195bd4dccb8..9e4d4056f17 100644 --- a/src/include/parser/parse_node.h +++ b/src/include/parser/parse_node.h @@ -371,7 +371,8 @@ extern SubscriptingRef *transformContainerSubscripts(ParseState *pstate, Oid containerType, int32 containerTypMod, List **indirection, - bool isAssignment); + bool isAssignment, + bool noError); extern Const *make_const(ParseState *pstate, A_Const *aconst); #endif /* PARSE_NODE_H */ -- 2.25.1 [text/x-patch] v6-0004-Implement-read-only-dot-notation-for-hstore.patch (4.4K, ../../[email protected]/6-v6-0004-Implement-read-only-dot-notation-for-hstore.patch) download | inline diff: From 5229a8407d48d1d4fadc765819bb6eded1f680d0 Mon Sep 17 00:00:00 2001 From: Nikita Glukhov <[email protected]> Date: Sat, 1 Apr 2023 16:32:37 +0300 Subject: [PATCH v6 04/11] Implement read-only dot notation for hstore --- contrib/hstore/expected/hstore.out | 14 +++++++++++++- contrib/hstore/hstore_subs.c | 25 ++++++++++++++++++------- contrib/hstore/sql/hstore.sql | 2 ++ 3 files changed, 33 insertions(+), 8 deletions(-) diff --git a/contrib/hstore/expected/hstore.out b/contrib/hstore/expected/hstore.out index 9e2421750fb..3dd2b2d7283 100644 --- a/contrib/hstore/expected/hstore.out +++ b/contrib/hstore/expected/hstore.out @@ -1604,12 +1604,20 @@ select f2['d'], f2['x'] is null as x_isnull from test_json_agg; | t (3 rows) +select (f2).d, (f2).x is null as x_isnull from test_json_agg; + d | x_isnull +--------+---------- + 12345 | t + -12345 | t + | t +(3 rows) + select f2['d']['e'] from test_json_agg; -- error ERROR: cannot subscript type text because it does not support subscripting LINE 1: select f2['d']['e'] from test_json_agg; ^ select f2['d':'e'] from test_json_agg; -- error -ERROR: hstore allows only one subscript +ERROR: hstore does not support slicing update test_json_agg set f2['d'] = f2['e'], f2['x'] = 'xyzzy'; select f2 from test_json_agg; f2 @@ -1619,6 +1627,10 @@ select f2 from test_json_agg; "d"=>NULL, "x"=>"xyzzy" (3 rows) +update test_json_agg set f2.d = (f2).x, f2."123" = 'aaa'; +ERROR: cannot assign to field "d" of column "f2" because its type hstore is not a composite type +LINE 1: update test_json_agg set f2.d = (f2).x, f2."123" = 'aaa'; + ^ -- Test subscripting in plpgsql do $$ declare h hstore; begin h['a'] := 'b'; raise notice 'h = %, h[a] = %', h, h['a']; end $$; diff --git a/contrib/hstore/hstore_subs.c b/contrib/hstore/hstore_subs.c index 304cee8c024..7c106c2268e 100644 --- a/contrib/hstore/hstore_subs.c +++ b/contrib/hstore/hstore_subs.c @@ -25,6 +25,7 @@ #include "executor/execExpr.h" #include "hstore.h" +#include "nodes/makefuncs.h" #include "nodes/nodeFuncs.h" #include "nodes/subscripting.h" #include "parser/parse_coerce.h" @@ -48,18 +49,22 @@ hstore_subscript_transform(SubscriptingRef *sbsref, Node *ind = linitial(*indirection); Node *subexpr; - /* We support only single-subscript, non-slice cases */ - if (isSlice || list_length(*indirection) < 1) - ereport(ERROR, - (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), - errmsg("hstore allows only one subscript"), - parser_errposition(pstate, - exprLocation((Node *) *indirection)))); + if (!*indirection) + return; if (IsA(ind, A_Indices)) { A_Indices *ai = castNode(A_Indices, ind); + /* We support only single-subscript, non-slice cases */ + if (ai->is_slice) + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("hstore does not support slicing"), + parser_errposition(pstate, + exprLocation((Node *) *indirection)))); + + /* Transform the subscript expression to type text */ ai = linitial_node(A_Indices, *indirection); Assert(ai->uidx != NULL && ai->lidx == NULL && !ai->is_slice); @@ -78,6 +83,12 @@ hstore_subscript_transform(SubscriptingRef *sbsref, errmsg("hstore subscript must have type text"), parser_errposition(pstate, exprLocation(ai->uidx)))); } + else if (IsA(ind, String)) + { + Datum field_txt = CStringGetTextDatum(strVal(ind)); + + subexpr = (Node *) makeConst(TEXTOID, -1, InvalidOid, -1, field_txt, false, false); + } else return; diff --git a/contrib/hstore/sql/hstore.sql b/contrib/hstore/sql/hstore.sql index efef91292a3..0502a63a27e 100644 --- a/contrib/hstore/sql/hstore.sql +++ b/contrib/hstore/sql/hstore.sql @@ -378,10 +378,12 @@ select json_agg(q) from (select f1, hstore_to_json_loose(f2) as f2 from test_jso -- Test subscripting insert into test_json_agg default values; select f2['d'], f2['x'] is null as x_isnull from test_json_agg; +select (f2).d, (f2).x is null as x_isnull from test_json_agg; select f2['d']['e'] from test_json_agg; -- error select f2['d':'e'] from test_json_agg; -- error update test_json_agg set f2['d'] = f2['e'], f2['x'] = 'xyzzy'; select f2 from test_json_agg; +update test_json_agg set f2.d = (f2).x, f2."123" = 'aaa'; -- Test subscripting in plpgsql do $$ declare h hstore; -- 2.25.1 [text/x-patch] v6-0005-Allow-processing-of-.-by-generic-subscripting.patch (8.8K, ../../[email protected]/7-v6-0005-Allow-processing-of-.-by-generic-subscripting.patch) download | inline diff: From 80ab3da1c748dd1abe9fd980d8de1862407d861d Mon Sep 17 00:00:00 2001 From: Nikita Glukhov <[email protected]> Date: Sat, 1 Apr 2023 23:15:26 +0300 Subject: [PATCH v6 05/11] Allow processing of .* by generic subscripting --- src/backend/parser/gram.y | 2 ++ src/backend/parser/parse_expr.c | 34 ++++++++++++------ src/backend/parser/parse_target.c | 60 ++++++++++++++++++++----------- src/backend/utils/adt/ruleutils.c | 6 +++- src/include/parser/parse_expr.h | 3 ++ 5 files changed, 73 insertions(+), 32 deletions(-) diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y index 67eb96396af..a8ee79518ca 100644 --- a/src/backend/parser/gram.y +++ b/src/backend/parser/gram.y @@ -18889,6 +18889,7 @@ check_func_name(List *names, core_yyscan_t yyscanner) static List * check_indirection(List *indirection, core_yyscan_t yyscanner) { +#if 0 ListCell *l; foreach(l, indirection) @@ -18899,6 +18900,7 @@ check_indirection(List *indirection, core_yyscan_t yyscanner) parser_yyerror("improper use of \"*\""); } } +#endif return indirection; } diff --git a/src/backend/parser/parse_expr.c b/src/backend/parser/parse_expr.c index 735d5c88729..3ed01864f66 100644 --- a/src/backend/parser/parse_expr.c +++ b/src/backend/parser/parse_expr.c @@ -75,7 +75,6 @@ static Node *transformColumnRef(ParseState *pstate, ColumnRef *cref); static Node *transformWholeRowRef(ParseState *pstate, ParseNamespaceItem *nsitem, int sublevels_up, int location); -static Node *transformIndirection(ParseState *pstate, A_Indirection *ind); static Node *transformTypeCast(ParseState *pstate, TypeCast *tc); static Node *transformCollateClause(ParseState *pstate, CollateClause *c); static Node *transformJsonObjectConstructor(ParseState *pstate, @@ -159,7 +158,7 @@ transformExprRecurse(ParseState *pstate, Node *expr) break; case T_A_Indirection: - result = transformIndirection(pstate, (A_Indirection *) expr); + result = transformIndirection(pstate, (A_Indirection *) expr, NULL); break; case T_A_ArrayExpr: @@ -433,8 +432,9 @@ unknown_attribute(ParseState *pstate, Node *relref, const char *attname, } } -static Node * -transformIndirection(ParseState *pstate, A_Indirection *ind) +Node * +transformIndirection(ParseState *pstate, A_Indirection *ind, + bool *trailing_star_expansion) { Node *last_srf = pstate->p_last_srf; Node *result = transformExprRecurse(pstate, ind->arg); @@ -454,12 +454,7 @@ transformIndirection(ParseState *pstate, A_Indirection *ind) if (IsA(n, A_Indices)) subscripts = lappend(subscripts, n); else if (IsA(n, A_Star)) - { - ereport(ERROR, - (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), - errmsg("row expansion via \"*\" is not supported here"), - parser_errposition(pstate, location))); - } + subscripts = lappend(subscripts, n); else { Assert(IsA(n, String)); @@ -526,7 +521,21 @@ transformIndirection(ParseState *pstate, A_Indirection *ind) n = linitial(subscripts); - if (!IsA(n, String)) + if (IsA(n, A_Star)) + { + /* Success, if trailing star expansion is allowed */ + if (trailing_star_expansion && list_length(subscripts) == 1) + { + *trailing_star_expansion = true; + return result; + } + + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("row expansion via \"*\" is not supported here"), + parser_errposition(pstate, location))); + } + else if (!IsA(n, String)) ereport(ERROR, (errcode(ERRCODE_DATATYPE_MISMATCH), errmsg("cannot subscript type %s because it does not support subscripting", @@ -552,6 +561,9 @@ transformIndirection(ParseState *pstate, A_Indirection *ind) result = newresult; } + if (trailing_star_expansion) + *trailing_star_expansion = false; + return result; } diff --git a/src/backend/parser/parse_target.c b/src/backend/parser/parse_target.c index 2dbcfd7488b..9041e424e1a 100644 --- a/src/backend/parser/parse_target.c +++ b/src/backend/parser/parse_target.c @@ -48,7 +48,7 @@ static Node *transformAssignmentSubscripts(ParseState *pstate, static List *ExpandColumnRefStar(ParseState *pstate, ColumnRef *cref, bool make_target_entry); static List *ExpandAllTables(ParseState *pstate, int location); -static List *ExpandIndirectionStar(ParseState *pstate, A_Indirection *ind, +static Node *ExpandIndirectionStar(ParseState *pstate, A_Indirection *ind, bool make_target_entry, ParseExprKind exprKind); static List *ExpandSingleTable(ParseState *pstate, ParseNamespaceItem *nsitem, int sublevels_up, int location, @@ -134,6 +134,7 @@ transformTargetList(ParseState *pstate, List *targetlist, foreach(o_target, targetlist) { ResTarget *res = (ResTarget *) lfirst(o_target); + Node *transformed = NULL; /* * Check for "something.*". Depending on the complexity of the @@ -162,13 +163,19 @@ transformTargetList(ParseState *pstate, List *targetlist, if (IsA(llast(ind->indirection), A_Star)) { - /* It is something.*, expand into multiple items */ - p_target = list_concat(p_target, - ExpandIndirectionStar(pstate, - ind, - true, - exprKind)); - continue; + Node *columns = ExpandIndirectionStar(pstate, + ind, + true, + exprKind); + + if (IsA(columns, List)) + { + /* It is something.*, expand into multiple items */ + p_target = list_concat(p_target, (List *) columns); + continue; + } + + transformed = (Node *) columns; } } } @@ -180,7 +187,7 @@ transformTargetList(ParseState *pstate, List *targetlist, p_target = lappend(p_target, transformTargetEntry(pstate, res->val, - NULL, + transformed, exprKind, res->name, false)); @@ -251,10 +258,15 @@ transformExpressionList(ParseState *pstate, List *exprlist, if (IsA(llast(ind->indirection), A_Star)) { - /* It is something.*, expand into multiple items */ - result = list_concat(result, - ExpandIndirectionStar(pstate, ind, - false, exprKind)); + Node *cols = ExpandIndirectionStar(pstate, ind, + false, exprKind); + + if (!cols || IsA(cols, List)) + /* It is something.*, expand into multiple items */ + result = list_concat(result, (List *) cols); + else + result = lappend(result, cols); + continue; } } @@ -1348,22 +1360,30 @@ ExpandAllTables(ParseState *pstate, int location) * For robustness, we use a separate "make_target_entry" flag to control * this rather than relying on exprKind. */ -static List * +static Node * ExpandIndirectionStar(ParseState *pstate, A_Indirection *ind, bool make_target_entry, ParseExprKind exprKind) { Node *expr; + ParseExprKind sv_expr_kind; + bool trailing_star_expansion = false; + + /* Save and restore identity of expression type we're parsing */ + Assert(exprKind != EXPR_KIND_NONE); + sv_expr_kind = pstate->p_expr_kind; + pstate->p_expr_kind = exprKind; /* Strip off the '*' to create a reference to the rowtype object */ - ind = copyObject(ind); - ind->indirection = list_truncate(ind->indirection, - list_length(ind->indirection) - 1); + expr = transformIndirection(pstate, ind, &trailing_star_expansion); + + pstate->p_expr_kind = sv_expr_kind; - /* And transform that */ - expr = transformExpr(pstate, (Node *) ind, exprKind); + /* '*' was consumed by generic type subscripting */ + if (!trailing_star_expansion) + return expr; /* Expand the rowtype expression into individual fields */ - return ExpandRowReference(pstate, expr, make_target_entry); + return (Node *) ExpandRowReference(pstate, expr, make_target_entry); } /* diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c index b0b25d8b9e5..fd92ac663d4 100644 --- a/src/backend/utils/adt/ruleutils.c +++ b/src/backend/utils/adt/ruleutils.c @@ -12841,7 +12841,11 @@ printSubscripts(SubscriptingRef *sbsref, deparse_context *context) { Node *up = (Node *) lfirst(uplist_item); - if (IsA(up, String)) + if (!up) + { + appendStringInfoString(buf, ".*"); + } + else if (IsA(up, String)) { appendStringInfoChar(buf, '.'); appendStringInfoString(buf, quote_identifier(strVal(up))); diff --git a/src/include/parser/parse_expr.h b/src/include/parser/parse_expr.h index f1f479fa56b..7f1889e9d84 100644 --- a/src/include/parser/parse_expr.h +++ b/src/include/parser/parse_expr.h @@ -23,4 +23,7 @@ extern Node *transformExpr(ParseState *pstate, Node *expr, ParseExprKind exprKin extern const char *ParseExprKindName(ParseExprKind exprKind); +extern Node *transformIndirection(ParseState *pstate, A_Indirection *ind, + bool *trailing_star_expansion); + #endif /* PARSE_EXPR_H */ -- 2.25.1 [text/x-patch] v6-0006-Export-jsonPathFromParseResult.patch (2.4K, ../../[email protected]/8-v6-0006-Export-jsonPathFromParseResult.patch) download | inline diff: From 5f2b15ddb0db67b733bdc4359b96645fb91a52c3 Mon Sep 17 00:00:00 2001 From: Nikita Glukhov <[email protected]> Date: Sat, 1 Apr 2023 23:15:55 +0300 Subject: [PATCH v6 06/11] Export jsonPathFromParseResult() --- src/backend/utils/adt/jsonpath.c | 24 ++++++++++++++++++------ src/include/utils/jsonpath.h | 4 ++++ 2 files changed, 22 insertions(+), 6 deletions(-) diff --git a/src/backend/utils/adt/jsonpath.c b/src/backend/utils/adt/jsonpath.c index 0f691bc5f0f..71946ff8370 100644 --- a/src/backend/utils/adt/jsonpath.c +++ b/src/backend/utils/adt/jsonpath.c @@ -165,16 +165,12 @@ jsonpath_send(PG_FUNCTION_ARGS) /* * Converts C-string to a jsonpath value. * - * Uses jsonpath parser to turn string into an AST, then - * flattenJsonPathParseItem() does second pass turning AST into binary - * representation of jsonpath. + * Uses jsonpath parser to turn string into an AST. */ static Datum jsonPathFromCstring(char *in, int len, struct Node *escontext) { JsonPathParseResult *jsonpath = parsejsonpath(in, len, escontext); - JsonPath *res; - StringInfoData buf; if (SOFT_ERROR_OCCURRED(escontext)) return (Datum) 0; @@ -185,8 +181,24 @@ jsonPathFromCstring(char *in, int len, struct Node *escontext) errmsg("invalid input syntax for type %s: \"%s\"", "jsonpath", in))); + return jsonPathFromParseResult(jsonpath, 4 * len /* estimation */, escontext); +} + +/* + * Converts jsonpath AST to a jsonpath value. + * + * flattenJsonPathParseItem() does second pass turning AST into binary + * representation of jsonpath. + */ +Datum +jsonPathFromParseResult(JsonPathParseResult *jsonpath, int estimated_len, + struct Node *escontext) +{ + JsonPath *res; + StringInfoData buf; + initStringInfo(&buf); - enlargeStringInfo(&buf, 4 * len /* estimation */ ); + enlargeStringInfo(&buf, estimated_len); appendStringInfoSpaces(&buf, JSONPATH_HDRSZ); diff --git a/src/include/utils/jsonpath.h b/src/include/utils/jsonpath.h index ee35698d083..4b8d43db634 100644 --- a/src/include/utils/jsonpath.h +++ b/src/include/utils/jsonpath.h @@ -281,6 +281,10 @@ extern JsonPathParseResult *parsejsonpath(const char *str, int len, extern bool jspConvertRegexFlags(uint32 xflags, int *result, struct Node *escontext); +extern Datum jsonPathFromParseResult(JsonPathParseResult *jsonpath, + int estimated_len, + struct Node *escontext); + /* * Struct for details about external variables passed into jsonpath executor */ -- 2.25.1 [text/x-patch] v6-0007-Implement-read-only-dot-notation-for-jsonb-using-.patch (32.5K, ../../[email protected]/9-v6-0007-Implement-read-only-dot-notation-for-jsonb-using-.patch) download | inline diff: From e837931dce5ae33c0622ea7fb5a931d658f48380 Mon Sep 17 00:00:00 2001 From: Nikita Glukhov <[email protected]> Date: Sat, 1 Apr 2023 23:17:53 +0300 Subject: [PATCH v6 07/11] Implement read-only dot notation for jsonb using jsonpath --- src/backend/utils/adt/jsonbsubs.c | 451 +++++++++++++++++++++++----- src/include/nodes/primnodes.h | 2 + src/test/regress/expected/jsonb.out | 336 ++++++++++++++++++++- src/test/regress/sql/jsonb.sql | 56 ++++ 4 files changed, 754 insertions(+), 91 deletions(-) diff --git a/src/backend/utils/adt/jsonbsubs.c b/src/backend/utils/adt/jsonbsubs.c index 6ecd320bc52..ddcb946e549 100644 --- a/src/backend/utils/adt/jsonbsubs.c +++ b/src/backend/utils/adt/jsonbsubs.c @@ -15,12 +15,14 @@ #include "postgres.h" #include "executor/execExpr.h" +#include "nodes/makefuncs.h" #include "nodes/nodeFuncs.h" #include "nodes/subscripting.h" #include "parser/parse_coerce.h" #include "parser/parse_expr.h" #include "utils/builtins.h" #include "utils/jsonb.h" +#include "utils/jsonpath.h" /* SubscriptingRefState.workspace for jsonb subscripting execution */ @@ -30,8 +32,274 @@ typedef struct JsonbSubWorkspace Oid *indexOid; /* OID of coerced subscript expression, could * be only integer or text */ Datum *index; /* Subscript values in Datum format */ + JsonPath *jsonpath; /* jsonpath for dot notation execution */ + List vars; /* jsonpath vars */ } JsonbSubWorkspace; +static bool +jsonb_check_jsonpath_needed(List *indirection) +{ + ListCell *lc; + + foreach(lc, indirection) + { + Node *accessor = lfirst(lc); + + if (IsA(accessor, String) || + IsA(accessor, A_Star)) + return true; + else if (IsA(accessor, A_Indices)) + { + A_Indices *ai = castNode(A_Indices, accessor); + + if (!ai->uidx || ai->lidx) + { + Assert(ai->is_slice); + return true; + } + } + else + return true; + } + + return false; +} + +static JsonPathParseItem * +make_jsonpath_item(JsonPathItemType type) +{ + JsonPathParseItem *v = palloc(sizeof(*v)); + + v->type = type; + v->next = NULL; + + return v; +} + +static JsonPathParseItem * +make_jsonpath_item_int(int32 val, List **exprs) +{ + JsonPathParseItem *jpi = make_jsonpath_item(jpiNumeric); + + jpi->value.numeric = + DatumGetNumeric(DirectFunctionCall1(int4_numeric, Int32GetDatum(val))); + + *exprs = lappend(*exprs, makeConst(INT4OID, -1, InvalidOid, 4, + Int32GetDatum(val), false, true)); + + return jpi; +} + +static Oid +jsonb_subscript_type(Node *expr) +{ + if (expr && IsA(expr, String)) + return TEXTOID; + + return exprType(expr); +} + +static Node * +coerce_jsonpath_subscript(ParseState *pstate, Node *subExpr, Oid numtype) +{ + Oid subExprType = jsonb_subscript_type(subExpr); + Oid targetType = UNKNOWNOID; + + if (subExprType != UNKNOWNOID) + { + Oid targets[2] = {numtype, TEXTOID}; + + /* + * Jsonb can handle multiple subscript types, but cases when a + * subscript could be coerced to multiple target types must be + * avoided, similar to overloaded functions. It could be + * possibly extend with jsonpath in the future. + */ + for (int i = 0; i < 2; i++) + { + if (can_coerce_type(1, &subExprType, &targets[i], COERCION_IMPLICIT)) + { + /* + * One type has already succeeded, it means there are + * two coercion targets possible, failure. + */ + if (targetType != UNKNOWNOID) + ereport(ERROR, + (errcode(ERRCODE_DATATYPE_MISMATCH), + errmsg("subscript type %s is not supported", format_type_be(subExprType)), + errhint("jsonb subscript must be coercible to only one type, integer or text."), + parser_errposition(pstate, exprLocation(subExpr)))); + + targetType = targets[i]; + } + } + + /* + * No suitable types were found, failure. + */ + if (targetType == UNKNOWNOID) + ereport(ERROR, + (errcode(ERRCODE_DATATYPE_MISMATCH), + errmsg("subscript type %s is not supported", format_type_be(subExprType)), + errhint("jsonb subscript must be coercible to either integer or text."), + parser_errposition(pstate, exprLocation(subExpr)))); + } + else + targetType = TEXTOID; + + /* + * We known from can_coerce_type that coercion will succeed, so + * coerce_type could be used. Note the implicit coercion context, + * which is required to handle subscripts of different types, + * similar to overloaded functions. + */ + subExpr = coerce_type(pstate, + subExpr, subExprType, + targetType, -1, + COERCION_IMPLICIT, + COERCE_IMPLICIT_CAST, + -1); + if (subExpr == NULL) + ereport(ERROR, + (errcode(ERRCODE_DATATYPE_MISMATCH), + errmsg("jsonb subscript must have text type"), + parser_errposition(pstate, exprLocation(subExpr)))); + + return subExpr; +} + +static JsonPathParseItem * +make_jsonpath_item_expr(ParseState *pstate, Node *expr, List **exprs) +{ + JsonPathParseItem *jpi; + + expr = transformExpr(pstate, expr, pstate->p_expr_kind); + + if (IsA(expr, Const)) + { + Const *cnst = (Const *) expr; + + if (cnst->consttype == INT4OID && !cnst->constisnull) + { + int32 val = DatumGetInt32(cnst->constvalue); + + return make_jsonpath_item_int(val, exprs); + } + } + + *exprs = lappend(*exprs, coerce_jsonpath_subscript(pstate, expr, NUMERICOID)); + + jpi = make_jsonpath_item(jpiVariable); + jpi->value.string.val = psprintf("%d", list_length(*exprs)); + jpi->value.string.len = strlen(jpi->value.string.val); + + return jpi; +} + +static Node * +jsonb_subscript_make_jsonpath(ParseState *pstate, List **indirection, + List **uexprs, List **lexprs) +{ + JsonPathParseResult jpres; + JsonPathParseItem *path = make_jsonpath_item(jpiRoot); + ListCell *lc; + Datum jsp; + int pathlen = 0; + + *uexprs = NIL; + *lexprs = NIL; + + jpres.expr = path; + jpres.lax = true; + + foreach(lc, *indirection) + { + Node *accessor = lfirst(lc); + JsonPathParseItem *jpi; + + if (IsA(accessor, String)) + { + char *field = strVal(accessor); + + jpi = make_jsonpath_item(jpiKey); + jpi->value.string.val = field; + jpi->value.string.len = strlen(field); + + *uexprs = lappend(*uexprs, accessor); + } + else if (IsA(accessor, A_Star)) + { + jpi = make_jsonpath_item(jpiAnyKey); + + *uexprs = lappend(*uexprs, NULL); + } + else if (IsA(accessor, A_Indices)) + { + A_Indices *ai = castNode(A_Indices, accessor); + + jpi = make_jsonpath_item(jpiIndexArray); + jpi->value.array.nelems = 1; + jpi->value.array.elems = palloc(sizeof(jpi->value.array.elems[0])); + + if (ai->is_slice) + { + while (list_length(*lexprs) < list_length(*uexprs)) + *lexprs = lappend(*lexprs, NULL); + + if (ai->lidx) + jpi->value.array.elems[0].from = make_jsonpath_item_expr(pstate, ai->lidx, lexprs); + else + jpi->value.array.elems[0].from = make_jsonpath_item_int(0, lexprs); + + if (ai->uidx) + jpi->value.array.elems[0].to = make_jsonpath_item_expr(pstate, ai->uidx, uexprs); + else + { + jpi->value.array.elems[0].to = make_jsonpath_item(jpiLast); + *uexprs = lappend(*uexprs, NULL); + } + } + else + { + Assert(ai->uidx && !ai->lidx); + jpi->value.array.elems[0].from = make_jsonpath_item_expr(pstate, ai->uidx, uexprs); + jpi->value.array.elems[0].to = NULL; + } + +#if 0 + if (jpi->value.array.elems[0].to) + { + while (list_length(*lower) < list_length(*upper)) + *lower = lappend(*lower, NULL); + + *lower = lappend(*lower, jpi->value.array.elems[0].from); + *upper = lappend(*upper, jpi->value.array.elems[0].to); + } + else + *upper = lappend(*upper, jpi->value.array.elems[0].from); +#endif + } + else + break; + + /* append path item */ + path->next = jpi; + path = jpi; + pathlen++; + } + + if (*lexprs) + { + while (list_length(*lexprs) < list_length(*uexprs)) + *lexprs = lappend(*lexprs, NULL); + } + + *indirection = list_delete_first_n(*indirection, pathlen); + + jsp = jsonPathFromParseResult(&jpres, 0, NULL); + + return (Node *) makeConst(JSONPATHOID, -1, InvalidOid, -1, jsp, false, false); +} /* * Finish parse analysis of a SubscriptingRef expression for a jsonb. @@ -49,19 +317,35 @@ jsonb_subscript_transform(SubscriptingRef *sbsref, List *upperIndexpr = NIL; ListCell *idx; + /* Determine the result type of the subscripting operation; always jsonb */ + sbsref->refrestype = JSONBOID; + sbsref->reftypmod = -1; + + if (jsonb_check_jsonpath_needed(*indirection)) + { + sbsref->refprivate = + jsonb_subscript_make_jsonpath(pstate, indirection, + &sbsref->refupperindexpr, + &sbsref->reflowerindexpr); + return; + } + /* * Transform and convert the subscript expressions. Jsonb subscripting * does not support slices, look only and the upper index. */ foreach(idx, *indirection) { + Node *i = lfirst(idx); A_Indices *ai; Node *subExpr; - if (!IsA(lfirst(idx), A_Indices)) + Assert(IsA(i, A_Indices)); + + if (!IsA(i, A_Indices)) break; - ai = lfirst_node(A_Indices, idx); + ai = castNode(A_Indices, i); if (isSlice) { @@ -75,71 +359,8 @@ jsonb_subscript_transform(SubscriptingRef *sbsref, if (ai->uidx) { - Oid subExprType = InvalidOid, - targetType = UNKNOWNOID; - subExpr = transformExpr(pstate, ai->uidx, pstate->p_expr_kind); - subExprType = exprType(subExpr); - - if (subExprType != UNKNOWNOID) - { - Oid targets[2] = {INT4OID, TEXTOID}; - - /* - * Jsonb can handle multiple subscript types, but cases when a - * subscript could be coerced to multiple target types must be - * avoided, similar to overloaded functions. It could be - * possibly extend with jsonpath in the future. - */ - for (int i = 0; i < 2; i++) - { - if (can_coerce_type(1, &subExprType, &targets[i], COERCION_IMPLICIT)) - { - /* - * One type has already succeeded, it means there are - * two coercion targets possible, failure. - */ - if (targetType != UNKNOWNOID) - ereport(ERROR, - (errcode(ERRCODE_DATATYPE_MISMATCH), - errmsg("subscript type %s is not supported", format_type_be(subExprType)), - errhint("jsonb subscript must be coercible to only one type, integer or text."), - parser_errposition(pstate, exprLocation(subExpr)))); - - targetType = targets[i]; - } - } - - /* - * No suitable types were found, failure. - */ - if (targetType == UNKNOWNOID) - ereport(ERROR, - (errcode(ERRCODE_DATATYPE_MISMATCH), - errmsg("subscript type %s is not supported", format_type_be(subExprType)), - errhint("jsonb subscript must be coercible to either integer or text."), - parser_errposition(pstate, exprLocation(subExpr)))); - } - else - targetType = TEXTOID; - - /* - * We known from can_coerce_type that coercion will succeed, so - * coerce_type could be used. Note the implicit coercion context, - * which is required to handle subscripts of different types, - * similar to overloaded functions. - */ - subExpr = coerce_type(pstate, - subExpr, subExprType, - targetType, -1, - COERCION_IMPLICIT, - COERCE_IMPLICIT_CAST, - -1); - if (subExpr == NULL) - ereport(ERROR, - (errcode(ERRCODE_DATATYPE_MISMATCH), - errmsg("jsonb subscript must have text type"), - parser_errposition(pstate, exprLocation(subExpr)))); + subExpr = coerce_jsonpath_subscript(pstate, subExpr, INT4OID); } else { @@ -161,10 +382,6 @@ jsonb_subscript_transform(SubscriptingRef *sbsref, sbsref->refupperindexpr = upperIndexpr; sbsref->reflowerindexpr = NIL; - /* Determine the result type of the subscripting operation; always jsonb */ - sbsref->refrestype = JSONBOID; - sbsref->reftypmod = -1; - /* Remove processed elements */ if (upperIndexpr) *indirection = list_delete_first_n(*indirection, list_length(upperIndexpr)); @@ -219,7 +436,7 @@ jsonb_subscript_check_subscripts(ExprState *state, * For jsonb fetch and assign functions we need to provide path in * text format. Convert if it's not already text. */ - if (workspace->indexOid[i] == INT4OID) + if (!workspace->jsonpath && workspace->indexOid[i] == INT4OID) { Datum datum = sbsrefstate->upperindex[i]; char *cs = DatumGetCString(DirectFunctionCall1(int4out, datum)); @@ -247,17 +464,44 @@ jsonb_subscript_fetch(ExprState *state, { SubscriptingRefState *sbsrefstate = op->d.sbsref.state; JsonbSubWorkspace *workspace = (JsonbSubWorkspace *) sbsrefstate->workspace; - Jsonb *jsonbSource; /* Should not get here if source jsonb (or any subscript) is null */ Assert(!(*op->resnull)); - jsonbSource = DatumGetJsonbP(*op->resvalue); - *op->resvalue = jsonb_get_element(jsonbSource, - workspace->index, - sbsrefstate->numupper, - op->resnull, - false); + if (workspace->jsonpath) + { + bool empty = false; + bool error = false; + List *vars = &workspace->vars; + ListCell *lc; + + /* copy computed variable values */ + foreach(lc, vars) + { + JsonPathVariable *var = lfirst(lc); + int i = foreach_current_index(lc); + + var->value = workspace->index[i]; + var->isnull = false; + } + + *op->resvalue = JsonPathQuery(*op->resvalue, workspace->jsonpath, + JSW_CONDITIONAL, + &empty, &error, vars, + /* FIXME */ "jsonb_subscript"); + + *op->resnull = empty || error; + } + else + { + Jsonb *jsonbSource = DatumGetJsonbP(*op->resvalue); + + *op->resvalue = jsonb_get_element(jsonbSource, + workspace->index, + sbsrefstate->numupper, + op->resnull, + false); + } } /* @@ -367,12 +611,57 @@ jsonb_exec_setup(const SubscriptingRef *sbsref, ListCell *lc; int nupper = sbsref->refupperindexpr->length; char *ptr; + bool useJsonpath = sbsref->refprivate != NULL; + JsonPathVariable *vars; + int nvars = useJsonpath ? nupper : 0; /* Allocate type-specific workspace with space for per-subscript data */ - workspace = palloc0(MAXALIGN(sizeof(JsonbSubWorkspace)) + + workspace = palloc0(MAXALIGN(offsetof(JsonbSubWorkspace, vars.initial_elements) + nvars * sizeof(ListCell)) + + MAXALIGN(nvars * sizeof(*vars) + nvars * 16) + nupper * (sizeof(Datum) + sizeof(Oid))); workspace->expectArray = false; - ptr = ((char *) workspace) + MAXALIGN(sizeof(JsonbSubWorkspace)); + ptr = ((char *) workspace) + + MAXALIGN(offsetof(JsonbSubWorkspace, vars.initial_elements) + + nvars * sizeof(ListCell)); + + if (!useJsonpath) + workspace->jsonpath = NULL; + else + { + workspace->jsonpath = DatumGetJsonPathP(castNode(Const, sbsref->refprivate)->constvalue); + + vars = (JsonPathVariable *) ptr; + ptr += MAXALIGN(nvars * sizeof(*vars)); + + workspace->vars.type = T_List; + workspace->vars.length = nvars; + workspace->vars.max_length = nvars; + workspace->vars.elements = &workspace->vars.initial_elements[0]; + + for (int i = 0; i < nvars; i++) + { + Node *expr = list_nth(sbsref->refupperindexpr, i); + + workspace->vars.elements[i].ptr_value = &vars[i]; + + if (expr && IsA(expr, String)) + { + vars[i].typid = TEXTOID; + vars[i].typmod = -1; + } + else + { + vars[i].typid = exprType(expr); + vars[i].typmod = exprTypmod(expr); + } + + vars[i].name = ptr; + snprintf(ptr, 16, "%d", i + 1); + vars[i].namelen = strlen(ptr); + + ptr += 16; + } + } /* * This coding assumes sizeof(Datum) >= sizeof(Oid), else we might @@ -390,7 +679,7 @@ jsonb_exec_setup(const SubscriptingRef *sbsref, Node *expr = lfirst(lc); int i = foreach_current_index(lc); - workspace->indexOid[i] = exprType(expr); + workspace->indexOid[i] = jsonb_subscript_type(expr); } /* diff --git a/src/include/nodes/primnodes.h b/src/include/nodes/primnodes.h index b0ef1952e8f..e2836960f2c 100644 --- a/src/include/nodes/primnodes.h +++ b/src/include/nodes/primnodes.h @@ -701,6 +701,8 @@ typedef struct SubscriptingRef Expr *refexpr; /* expression for the source value, or NULL if fetch */ Expr *refassgnexpr; + /* private expression */ + Node *refprivate; } SubscriptingRef; /* diff --git a/src/test/regress/expected/jsonb.out b/src/test/regress/expected/jsonb.out index 7d163a156e3..95a93a582b1 100644 --- a/src/test/regress/expected/jsonb.out +++ b/src/test/regress/expected/jsonb.out @@ -4939,6 +4939,12 @@ select ('123'::jsonb)['a']; (1 row) +select ('123'::jsonb).a; + a +--- + +(1 row) + select ('123'::jsonb)[0]; jsonb ------- @@ -4957,6 +4963,12 @@ select ('{"a": 1}'::jsonb)['a']; 1 (1 row) +select ('{"a": 1}'::jsonb).a; + a +--- + 1 +(1 row) + select ('{"a": 1}'::jsonb)[0]; jsonb ------- @@ -4969,6 +4981,12 @@ select ('{"a": 1}'::jsonb)['not_exist']; (1 row) +select ('{"a": 1}'::jsonb)."not_exist"; + not_exist +----------- + +(1 row) + select ('{"a": 1}'::jsonb)[NULL]; jsonb ------- @@ -4981,6 +4999,12 @@ select ('[1, "2", null]'::jsonb)['a']; (1 row) +select ('[1, "2", null]'::jsonb).a; + a +--- + +(1 row) + select ('[1, "2", null]'::jsonb)[0]; jsonb ------- @@ -4993,6 +5017,12 @@ select ('[1, "2", null]'::jsonb)['1']; "2" (1 row) +select ('[1, "2", null]'::jsonb)."1"; + 1 +--- + +(1 row) + select ('[1, "2", null]'::jsonb)[1.0]; ERROR: subscript type numeric is not supported LINE 1: select ('[1, "2", null]'::jsonb)[1.0]; @@ -5022,6 +5052,12 @@ select ('[1, "2", null]'::jsonb)[1]['a']; (1 row) +select ('[1, "2", null]'::jsonb)[1].a; + a +--- + +(1 row) + select ('[1, "2", null]'::jsonb)[1][0]; jsonb ------- @@ -5034,73 +5070,143 @@ select ('{"a": 1, "b": "c", "d": [1, 2, 3]}'::jsonb)['b']; "c" (1 row) +select ('{"a": 1, "b": "c", "d": [1, 2, 3]}'::jsonb).b; + b +----- + "c" +(1 row) + select ('{"a": 1, "b": "c", "d": [1, 2, 3]}'::jsonb)['d']; jsonb ----------- [1, 2, 3] (1 row) +select ('{"a": 1, "b": "c", "d": [1, 2, 3]}'::jsonb).d; + d +----------- + [1, 2, 3] +(1 row) + select ('{"a": 1, "b": "c", "d": [1, 2, 3]}'::jsonb)['d'][1]; jsonb ------- 2 (1 row) +select ('{"a": 1, "b": "c", "d": [1, 2, 3]}'::jsonb).d[1]; + d +--- + 2 +(1 row) + select ('{"a": 1, "b": "c", "d": [1, 2, 3]}'::jsonb)['d']['a']; jsonb ------- (1 row) +select ('{"a": 1, "b": "c", "d": [1, 2, 3]}'::jsonb).d['a']; + d +--- + +(1 row) + +select ('{"a": 1, "b": "c", "d": [1, 2, 3]}'::jsonb).d.a; + a +--- + +(1 row) + select ('{"a": {"a1": {"a2": "aaa"}}, "b": "bbb", "c": "ccc"}'::jsonb)['a']['a1']; jsonb --------------- {"a2": "aaa"} (1 row) +select ('{"a": {"a1": {"a2": "aaa"}}, "b": "bbb", "c": "ccc"}'::jsonb).a.a1; + a1 +--------------- + {"a2": "aaa"} +(1 row) + select ('{"a": {"a1": {"a2": "aaa"}}, "b": "bbb", "c": "ccc"}'::jsonb)['a']['a1']['a2']; jsonb ------- "aaa" (1 row) +select ('{"a": {"a1": {"a2": "aaa"}}, "b": "bbb", "c": "ccc"}'::jsonb).a.a1.a2; + a2 +------- + "aaa" +(1 row) + select ('{"a": {"a1": {"a2": "aaa"}}, "b": "bbb", "c": "ccc"}'::jsonb)['a']['a1']['a2']['a3']; jsonb ------- (1 row) +select ('{"a": {"a1": {"a2": "aaa"}}, "b": "bbb", "c": "ccc"}'::jsonb).a.a1.a2.a3; + a3 +---- + +(1 row) + select ('{"a": ["a1", {"b1": ["aaa", "bbb", "ccc"]}], "b": "bb"}'::jsonb)['a'][1]['b1']; jsonb ----------------------- ["aaa", "bbb", "ccc"] (1 row) +select ('{"a": ["a1", {"b1": ["aaa", "bbb", "ccc"]}], "b": "bb"}'::jsonb).a[1].b1; + b1 +----------------------- + ["aaa", "bbb", "ccc"] +(1 row) + select ('{"a": ["a1", {"b1": ["aaa", "bbb", "ccc"]}], "b": "bb"}'::jsonb)['a'][1]['b1'][2]; jsonb ------- "ccc" (1 row) +select ('{"a": ["a1", {"b1": ["aaa", "bbb", "ccc"]}], "b": "bb"}'::jsonb).a[1].b1[2]; + b1 +------- + "ccc" +(1 row) + -- slices are not supported select ('{"a": 1}'::jsonb)['a':'b']; -ERROR: jsonb subscript does not support slices -LINE 1: select ('{"a": 1}'::jsonb)['a':'b']; - ^ + jsonb +------- + +(1 row) + select ('[1, "2", null]'::jsonb)[1:2]; -ERROR: jsonb subscript does not support slices -LINE 1: select ('[1, "2", null]'::jsonb)[1:2]; - ^ + jsonb +------------- + ["2", null] +(1 row) + select ('[1, "2", null]'::jsonb)[:2]; ERROR: jsonb subscript does not support slices LINE 1: select ('[1, "2", null]'::jsonb)[:2]; ^ select ('[1, "2", null]'::jsonb)[1:]; -ERROR: jsonb subscript does not support slices -LINE 1: select ('[1, "2", null]'::jsonb)[1:]; - ^ + jsonb +------------- + ["2", null] +(1 row) + select ('[1, "2", null]'::jsonb)[:]; -ERROR: jsonb subscript does not support slices + jsonb +---------------- + [1, "2", null] +(1 row) + create TEMP TABLE test_jsonb_subscript ( id int, test_json jsonb @@ -5715,3 +5821,213 @@ select '12345.0000000000000000000000000000000000000000000005'::jsonb::int8; 12345 (1 row) +-- dot notation +CREATE TABLE test_jsonb_dot_notation AS +SELECT '{"a": [1, 2, {"b": "c"}, {"b": "d", "e": "f", "x": {"y": "yyy", "z": "zzz"}}], "b": [3, 4, {"b": "g", "x": {"y": "YYY", "z": "ZZZ"}}]}'::jsonb jb; +SELECT (jb).* FROM test_jsonb_dot_notation; + jb +------------------------------------------------------------------------------------------------------------------------------ + [[1, 2, {"b": "c"}, {"b": "d", "e": "f", "x": {"y": "yyy", "z": "zzz"}}], [3, 4, {"b": "g", "x": {"y": "YYY", "z": "ZZZ"}}]] +(1 row) + +SELECT (jb).* FROM test_jsonb_dot_notation t; + jb +------------------------------------------------------------------------------------------------------------------------------ + [[1, 2, {"b": "c"}, {"b": "d", "e": "f", "x": {"y": "yyy", "z": "zzz"}}], [3, 4, {"b": "g", "x": {"y": "YYY", "z": "ZZZ"}}]] +(1 row) + +SELECT (t.jb).* FROM test_jsonb_dot_notation t; + jb +------------------------------------------------------------------------------------------------------------------------------ + [[1, 2, {"b": "c"}, {"b": "d", "e": "f", "x": {"y": "yyy", "z": "zzz"}}], [3, 4, {"b": "g", "x": {"y": "YYY", "z": "ZZZ"}}]] +(1 row) + +SELECT (jb).* FROM test_jsonb_dot_notation; + jb +------------------------------------------------------------------------------------------------------------------------------ + [[1, 2, {"b": "c"}, {"b": "d", "e": "f", "x": {"y": "yyy", "z": "zzz"}}], [3, 4, {"b": "g", "x": {"y": "YYY", "z": "ZZZ"}}]] +(1 row) + +SELECT (t.jb).* FROM test_jsonb_dot_notation; +ERROR: missing FROM-clause entry for table "t" +LINE 1: SELECT (t.jb).* FROM test_jsonb_dot_notation; + ^ +SELECT (t.jb).* FROM test_jsonb_dot_notation t; + jb +------------------------------------------------------------------------------------------------------------------------------ + [[1, 2, {"b": "c"}, {"b": "d", "e": "f", "x": {"y": "yyy", "z": "zzz"}}], [3, 4, {"b": "g", "x": {"y": "YYY", "z": "ZZZ"}}]] +(1 row) + +SELECT (jb).a FROM test_jsonb_dot_notation; + a +------------------------------------------------------------------------- + [1, 2, {"b": "c"}, {"b": "d", "e": "f", "x": {"y": "yyy", "z": "zzz"}}] +(1 row) + +SELECT (jb).a FROM test_jsonb_dot_notation; + a +------------------------------------------------------------------------- + [1, 2, {"b": "c"}, {"b": "d", "e": "f", "x": {"y": "yyy", "z": "zzz"}}] +(1 row) + +SELECT (jb).b FROM test_jsonb_dot_notation; + b +--------------------------------------------------- + [3, 4, {"b": "g", "x": {"y": "YYY", "z": "ZZZ"}}] +(1 row) + +SELECT (jb).c FROM test_jsonb_dot_notation; + c +--- + +(1 row) + +SELECT (jb).a.b FROM test_jsonb_dot_notation; + b +------------ + ["c", "d"] +(1 row) + +SELECT (jb).a.* FROM test_jsonb_dot_notation; + a +------------------------------------------- + ["c", "d", "f", {"y": "yyy", "z": "zzz"}] +(1 row) + +SELECT (jb).a.*.b FROM test_jsonb_dot_notation; + b +--- + +(1 row) + +SELECT (jb).a.*.x FROM test_jsonb_dot_notation; + x +--- + +(1 row) + +SELECT (jb).a.*.y FROM test_jsonb_dot_notation; + y +------- + "yyy" +(1 row) + +SELECT (jb).a.*.* FROM test_jsonb_dot_notation; + a +---------------- + ["yyy", "zzz"] +(1 row) + +SELECT (jb).*.x FROM test_jsonb_dot_notation; + x +------------------------------------------------------ + [{"y": "yyy", "z": "zzz"}, {"y": "YYY", "z": "ZZZ"}] +(1 row) + +SELECT (jb).*.x FROM test_jsonb_dot_notation t; + x +------------------------------------------------------ + [{"y": "yyy", "z": "zzz"}, {"y": "YYY", "z": "ZZZ"}] +(1 row) + +SELECT ((jb).*).x FROM test_jsonb_dot_notation t; + x +--- + +(1 row) + +SELECT ((jb).*).x FROM test_jsonb_dot_notation t; + x +--- + +(1 row) + +SELECT ((jb).*)[:].x FROM test_jsonb_dot_notation t; + x +------------------------------------------------------ + [{"y": "yyy", "z": "zzz"}, {"y": "YYY", "z": "ZZZ"}] +(1 row) + +SELECT (jb).*.x FROM test_jsonb_dot_notation; + x +------------------------------------------------------ + [{"y": "yyy", "z": "zzz"}, {"y": "YYY", "z": "ZZZ"}] +(1 row) + +SELECT (jb).*.x.* FROM test_jsonb_dot_notation; + x +------------------------------ + ["yyy", "zzz", "YYY", "ZZZ"] +(1 row) + +SELECT (jb).*.x.y FROM test_jsonb_dot_notation; + y +---------------- + ["yyy", "YYY"] +(1 row) + +SELECT (jb).*.x.z FROM test_jsonb_dot_notation; + z +---------------- + ["zzz", "ZZZ"] +(1 row) + +SELECT (jb).*.*.y FROM test_jsonb_dot_notation; + y +---------------- + ["yyy", "YYY"] +(1 row) + +SELECT (jb).*.*.* FROM test_jsonb_dot_notation; + jb +------------------------------ + ["yyy", "zzz", "YYY", "ZZZ"] +(1 row) + +SELECT (jb).*.*.*.* FROM test_jsonb_dot_notation; + jb +---- + +(1 row) + +SELECT (jb).a.b.c.* FROM test_jsonb_dot_notation; + c +--- + +(1 row) + +EXPLAIN (VERBOSE, COSTS OFF) SELECT (jb).* FROM test_jsonb_dot_notation; + QUERY PLAN +-------------------------------------------- + Seq Scan on public.test_jsonb_dot_notation + Output: jb.* +(2 rows) + +EXPLAIN (VERBOSE, COSTS OFF) SELECT (jb).a FROM test_jsonb_dot_notation; + QUERY PLAN +-------------------------------------------- + Seq Scan on public.test_jsonb_dot_notation + Output: jb.a +(2 rows) + +EXPLAIN (VERBOSE, COSTS OFF) SELECT (jb).a[1] FROM test_jsonb_dot_notation; + QUERY PLAN +-------------------------------------------- + Seq Scan on public.test_jsonb_dot_notation + Output: jb.a[1] +(2 rows) + +EXPLAIN (VERBOSE, COSTS OFF) SELECT (jb).a.*['b'] FROM test_jsonb_dot_notation; + QUERY PLAN +-------------------------------------------- + Seq Scan on public.test_jsonb_dot_notation + Output: jb.a.*['b'::text] +(2 rows) + +EXPLAIN (VERBOSE, COSTS OFF) SELECT (jb).a.*[1:2]['b'].b FROM test_jsonb_dot_notation; + QUERY PLAN +-------------------------------------------- + Seq Scan on public.test_jsonb_dot_notation + Output: jb.a.*[1:2][:'b'::text].b +(2 rows) + diff --git a/src/test/regress/sql/jsonb.sql b/src/test/regress/sql/jsonb.sql index 5f0190d5a2b..e3b81efa2d0 100644 --- a/src/test/regress/sql/jsonb.sql +++ b/src/test/regress/sql/jsonb.sql @@ -1286,30 +1286,46 @@ select jsonb_insert('{"a": {"b": "value"}}', '{a, b}', '"new_value"', true); -- jsonb subscript select ('123'::jsonb)['a']; +select ('123'::jsonb).a; select ('123'::jsonb)[0]; select ('123'::jsonb)[NULL]; select ('{"a": 1}'::jsonb)['a']; +select ('{"a": 1}'::jsonb).a; select ('{"a": 1}'::jsonb)[0]; select ('{"a": 1}'::jsonb)['not_exist']; +select ('{"a": 1}'::jsonb)."not_exist"; select ('{"a": 1}'::jsonb)[NULL]; select ('[1, "2", null]'::jsonb)['a']; +select ('[1, "2", null]'::jsonb).a; select ('[1, "2", null]'::jsonb)[0]; select ('[1, "2", null]'::jsonb)['1']; +select ('[1, "2", null]'::jsonb)."1"; select ('[1, "2", null]'::jsonb)[1.0]; select ('[1, "2", null]'::jsonb)[2]; select ('[1, "2", null]'::jsonb)[3]; select ('[1, "2", null]'::jsonb)[-2]; select ('[1, "2", null]'::jsonb)[1]['a']; +select ('[1, "2", null]'::jsonb)[1].a; select ('[1, "2", null]'::jsonb)[1][0]; select ('{"a": 1, "b": "c", "d": [1, 2, 3]}'::jsonb)['b']; +select ('{"a": 1, "b": "c", "d": [1, 2, 3]}'::jsonb).b; select ('{"a": 1, "b": "c", "d": [1, 2, 3]}'::jsonb)['d']; +select ('{"a": 1, "b": "c", "d": [1, 2, 3]}'::jsonb).d; select ('{"a": 1, "b": "c", "d": [1, 2, 3]}'::jsonb)['d'][1]; +select ('{"a": 1, "b": "c", "d": [1, 2, 3]}'::jsonb).d[1]; select ('{"a": 1, "b": "c", "d": [1, 2, 3]}'::jsonb)['d']['a']; +select ('{"a": 1, "b": "c", "d": [1, 2, 3]}'::jsonb).d['a']; +select ('{"a": 1, "b": "c", "d": [1, 2, 3]}'::jsonb).d.a; select ('{"a": {"a1": {"a2": "aaa"}}, "b": "bbb", "c": "ccc"}'::jsonb)['a']['a1']; +select ('{"a": {"a1": {"a2": "aaa"}}, "b": "bbb", "c": "ccc"}'::jsonb).a.a1; select ('{"a": {"a1": {"a2": "aaa"}}, "b": "bbb", "c": "ccc"}'::jsonb)['a']['a1']['a2']; +select ('{"a": {"a1": {"a2": "aaa"}}, "b": "bbb", "c": "ccc"}'::jsonb).a.a1.a2; select ('{"a": {"a1": {"a2": "aaa"}}, "b": "bbb", "c": "ccc"}'::jsonb)['a']['a1']['a2']['a3']; +select ('{"a": {"a1": {"a2": "aaa"}}, "b": "bbb", "c": "ccc"}'::jsonb).a.a1.a2.a3; select ('{"a": ["a1", {"b1": ["aaa", "bbb", "ccc"]}], "b": "bb"}'::jsonb)['a'][1]['b1']; +select ('{"a": ["a1", {"b1": ["aaa", "bbb", "ccc"]}], "b": "bb"}'::jsonb).a[1].b1; select ('{"a": ["a1", {"b1": ["aaa", "bbb", "ccc"]}], "b": "bb"}'::jsonb)['a'][1]['b1'][2]; +select ('{"a": ["a1", {"b1": ["aaa", "bbb", "ccc"]}], "b": "bb"}'::jsonb).a[1].b1[2]; -- slices are not supported select ('{"a": 1}'::jsonb)['a':'b']; @@ -1559,3 +1575,43 @@ select '12345.0000000000000000000000000000000000000000000005'::jsonb::float8; select '12345.0000000000000000000000000000000000000000000005'::jsonb::int2; select '12345.0000000000000000000000000000000000000000000005'::jsonb::int4; select '12345.0000000000000000000000000000000000000000000005'::jsonb::int8; + +-- dot notation +CREATE TABLE test_jsonb_dot_notation AS +SELECT '{"a": [1, 2, {"b": "c"}, {"b": "d", "e": "f", "x": {"y": "yyy", "z": "zzz"}}], "b": [3, 4, {"b": "g", "x": {"y": "YYY", "z": "ZZZ"}}]}'::jsonb jb; + +SELECT (jb).* FROM test_jsonb_dot_notation; +SELECT (jb).* FROM test_jsonb_dot_notation t; +SELECT (t.jb).* FROM test_jsonb_dot_notation t; +SELECT (jb).* FROM test_jsonb_dot_notation; +SELECT (t.jb).* FROM test_jsonb_dot_notation; +SELECT (t.jb).* FROM test_jsonb_dot_notation t; +SELECT (jb).a FROM test_jsonb_dot_notation; +SELECT (jb).a FROM test_jsonb_dot_notation; +SELECT (jb).b FROM test_jsonb_dot_notation; +SELECT (jb).c FROM test_jsonb_dot_notation; +SELECT (jb).a.b FROM test_jsonb_dot_notation; +SELECT (jb).a.* FROM test_jsonb_dot_notation; +SELECT (jb).a.*.b FROM test_jsonb_dot_notation; +SELECT (jb).a.*.x FROM test_jsonb_dot_notation; +SELECT (jb).a.*.y FROM test_jsonb_dot_notation; +SELECT (jb).a.*.* FROM test_jsonb_dot_notation; +SELECT (jb).*.x FROM test_jsonb_dot_notation; +SELECT (jb).*.x FROM test_jsonb_dot_notation t; +SELECT ((jb).*).x FROM test_jsonb_dot_notation t; +SELECT ((jb).*).x FROM test_jsonb_dot_notation t; +SELECT ((jb).*)[:].x FROM test_jsonb_dot_notation t; +SELECT (jb).*.x FROM test_jsonb_dot_notation; +SELECT (jb).*.x.* FROM test_jsonb_dot_notation; +SELECT (jb).*.x.y FROM test_jsonb_dot_notation; +SELECT (jb).*.x.z FROM test_jsonb_dot_notation; +SELECT (jb).*.*.y FROM test_jsonb_dot_notation; +SELECT (jb).*.*.* FROM test_jsonb_dot_notation; +SELECT (jb).*.*.*.* FROM test_jsonb_dot_notation; +SELECT (jb).a.b.c.* FROM test_jsonb_dot_notation; + +EXPLAIN (VERBOSE, COSTS OFF) SELECT (jb).* FROM test_jsonb_dot_notation; +EXPLAIN (VERBOSE, COSTS OFF) SELECT (jb).a FROM test_jsonb_dot_notation; +EXPLAIN (VERBOSE, COSTS OFF) SELECT (jb).a[1] FROM test_jsonb_dot_notation; +EXPLAIN (VERBOSE, COSTS OFF) SELECT (jb).a.*['b'] FROM test_jsonb_dot_notation; +EXPLAIN (VERBOSE, COSTS OFF) SELECT (jb).a.*[1:2]['b'].b FROM test_jsonb_dot_notation; -- 2.25.1 [text/x-patch] v6-0008-Extract-transformColumnRefInternal.patch (8.3K, ../../[email protected]/10-v6-0008-Extract-transformColumnRefInternal.patch) download | inline diff: From face4d14fb335204d024456e63b6dca7bdc1f97d Mon Sep 17 00:00:00 2001 From: Nikita Glukhov <[email protected]> Date: Sun, 2 Apr 2023 00:13:38 +0300 Subject: [PATCH v6 08/11] Extract transformColumnRefInternal() --- src/backend/parser/parse_expr.c | 238 ++++++++++++++++++-------------- 1 file changed, 132 insertions(+), 106 deletions(-) diff --git a/src/backend/parser/parse_expr.c b/src/backend/parser/parse_expr.c index 3ed01864f66..b82d25c40a4 100644 --- a/src/backend/parser/parse_expr.c +++ b/src/backend/parser/parse_expr.c @@ -567,116 +567,31 @@ transformIndirection(ParseState *pstate, A_Indirection *ind, return result; } -/* - * Transform a ColumnRef. - * - * If you find yourself changing this code, see also ExpandColumnRefStar. - */ -static Node * -transformColumnRef(ParseState *pstate, ColumnRef *cref) +typedef struct ColRefError { - Node *node = NULL; - char *nspname = NULL; - char *relname = NULL; - char *colname = NULL; - ParseNamespaceItem *nsitem; - int levels_up; enum { CRERR_NO_COLUMN, CRERR_NO_RTE, CRERR_WRONG_DB, CRERR_TOO_MANY - } crerr = CRERR_NO_COLUMN; - const char *err; + } code; + char *nspname; + char *relname; + char *colname; +} ColRefError; - /* - * Check to see if the column reference is in an invalid place within the - * query. We allow column references in most places, except in default - * expressions and partition bound expressions. - */ - err = NULL; - switch (pstate->p_expr_kind) - { - case EXPR_KIND_NONE: - Assert(false); /* can't happen */ - break; - case EXPR_KIND_OTHER: - case EXPR_KIND_JOIN_ON: - case EXPR_KIND_JOIN_USING: - case EXPR_KIND_FROM_SUBSELECT: - case EXPR_KIND_FROM_FUNCTION: - case EXPR_KIND_WHERE: - case EXPR_KIND_POLICY: - case EXPR_KIND_HAVING: - case EXPR_KIND_FILTER: - case EXPR_KIND_WINDOW_PARTITION: - case EXPR_KIND_WINDOW_ORDER: - case EXPR_KIND_WINDOW_FRAME_RANGE: - case EXPR_KIND_WINDOW_FRAME_ROWS: - case EXPR_KIND_WINDOW_FRAME_GROUPS: - case EXPR_KIND_SELECT_TARGET: - case EXPR_KIND_INSERT_TARGET: - case EXPR_KIND_UPDATE_SOURCE: - case EXPR_KIND_UPDATE_TARGET: - case EXPR_KIND_MERGE_WHEN: - case EXPR_KIND_GROUP_BY: - case EXPR_KIND_ORDER_BY: - case EXPR_KIND_DISTINCT_ON: - case EXPR_KIND_LIMIT: - case EXPR_KIND_OFFSET: - case EXPR_KIND_RETURNING: - case EXPR_KIND_MERGE_RETURNING: - case EXPR_KIND_VALUES: - case EXPR_KIND_VALUES_SINGLE: - case EXPR_KIND_CHECK_CONSTRAINT: - case EXPR_KIND_DOMAIN_CHECK: - case EXPR_KIND_FUNCTION_DEFAULT: - case EXPR_KIND_INDEX_EXPRESSION: - case EXPR_KIND_INDEX_PREDICATE: - case EXPR_KIND_STATS_EXPRESSION: - case EXPR_KIND_ALTER_COL_TRANSFORM: - case EXPR_KIND_EXECUTE_PARAMETER: - case EXPR_KIND_TRIGGER_WHEN: - case EXPR_KIND_PARTITION_EXPRESSION: - case EXPR_KIND_CALL_ARGUMENT: - case EXPR_KIND_COPY_WHERE: - case EXPR_KIND_GENERATED_COLUMN: - case EXPR_KIND_CYCLE_MARK: - /* okay */ - break; - - case EXPR_KIND_COLUMN_DEFAULT: - err = _("cannot use column reference in DEFAULT expression"); - break; - case EXPR_KIND_PARTITION_BOUND: - err = _("cannot use column reference in partition bound expression"); - break; - - /* - * There is intentionally no default: case here, so that the - * compiler will warn if we add a new ParseExprKind without - * extending this switch. If we do see an unrecognized value at - * runtime, the behavior will be the same as for EXPR_KIND_OTHER, - * which is sane anyway. - */ - } - if (err) - ereport(ERROR, - (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), - errmsg_internal("%s", err), - parser_errposition(pstate, cref->location))); - - /* - * Give the PreParseColumnRefHook, if any, first shot. If it returns - * non-null then that's all, folks. - */ - if (pstate->p_pre_columnref_hook != NULL) - { - node = pstate->p_pre_columnref_hook(pstate, cref); - if (node != NULL) - return node; - } +static Node * +transformColumnRefInternal(ParseState *pstate, ColumnRef *cref, int nfields, + ColRefError *err) +{ + ParseNamespaceItem *nsitem; + Node *node = NULL; + char *nspname = NULL; + char *relname = NULL; + char *colname = NULL; + int crerr = CRERR_NO_COLUMN; + int levels_up; /*---------- * The allowed syntaxes are: @@ -701,7 +616,7 @@ transformColumnRef(ParseState *pstate, ColumnRef *cref) * database name; we check it here and then discard it. *---------- */ - switch (list_length(cref->fields)) + switch (nfields) { case 1: { @@ -890,6 +805,117 @@ transformColumnRef(ParseState *pstate, ColumnRef *cref) break; } + err->code = crerr; + err->nspname = nspname; + err->relname = relname; + err->colname = colname; + + return node; +} + +/* + * Transform a ColumnRef. + * + * If you find yourself changing this code, see also ExpandColumnRefStar. + */ +static Node * +transformColumnRef(ParseState *pstate, ColumnRef *cref) +{ + Node *node = NULL; + ColRefError crerr; + const char *err; + + /* + * Check to see if the column reference is in an invalid place within the + * query. We allow column references in most places, except in default + * expressions and partition bound expressions. + */ + err = NULL; + switch (pstate->p_expr_kind) + { + case EXPR_KIND_NONE: + Assert(false); /* can't happen */ + break; + case EXPR_KIND_OTHER: + case EXPR_KIND_JOIN_ON: + case EXPR_KIND_JOIN_USING: + case EXPR_KIND_FROM_SUBSELECT: + case EXPR_KIND_FROM_FUNCTION: + case EXPR_KIND_WHERE: + case EXPR_KIND_POLICY: + case EXPR_KIND_HAVING: + case EXPR_KIND_FILTER: + case EXPR_KIND_WINDOW_PARTITION: + case EXPR_KIND_WINDOW_ORDER: + case EXPR_KIND_WINDOW_FRAME_RANGE: + case EXPR_KIND_WINDOW_FRAME_ROWS: + case EXPR_KIND_WINDOW_FRAME_GROUPS: + case EXPR_KIND_SELECT_TARGET: + case EXPR_KIND_INSERT_TARGET: + case EXPR_KIND_UPDATE_SOURCE: + case EXPR_KIND_UPDATE_TARGET: + case EXPR_KIND_MERGE_WHEN: + case EXPR_KIND_GROUP_BY: + case EXPR_KIND_ORDER_BY: + case EXPR_KIND_DISTINCT_ON: + case EXPR_KIND_LIMIT: + case EXPR_KIND_OFFSET: + case EXPR_KIND_RETURNING: + case EXPR_KIND_MERGE_RETURNING: + case EXPR_KIND_VALUES: + case EXPR_KIND_VALUES_SINGLE: + case EXPR_KIND_CHECK_CONSTRAINT: + case EXPR_KIND_DOMAIN_CHECK: + case EXPR_KIND_FUNCTION_DEFAULT: + case EXPR_KIND_INDEX_EXPRESSION: + case EXPR_KIND_INDEX_PREDICATE: + case EXPR_KIND_STATS_EXPRESSION: + case EXPR_KIND_ALTER_COL_TRANSFORM: + case EXPR_KIND_EXECUTE_PARAMETER: + case EXPR_KIND_TRIGGER_WHEN: + case EXPR_KIND_PARTITION_EXPRESSION: + case EXPR_KIND_CALL_ARGUMENT: + case EXPR_KIND_COPY_WHERE: + case EXPR_KIND_GENERATED_COLUMN: + case EXPR_KIND_CYCLE_MARK: + /* okay */ + break; + + case EXPR_KIND_COLUMN_DEFAULT: + err = _("cannot use column reference in DEFAULT expression"); + break; + case EXPR_KIND_PARTITION_BOUND: + err = _("cannot use column reference in partition bound expression"); + break; + + /* + * There is intentionally no default: case here, so that the + * compiler will warn if we add a new ParseExprKind without + * extending this switch. If we do see an unrecognized value at + * runtime, the behavior will be the same as for EXPR_KIND_OTHER, + * which is sane anyway. + */ + } + if (err) + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg_internal("%s", err), + parser_errposition(pstate, cref->location))); + + /* + * Give the PreParseColumnRefHook, if any, first shot. If it returns + * non-null then that's all, folks. + */ + if (pstate->p_pre_columnref_hook != NULL) + { + node = pstate->p_pre_columnref_hook(pstate, cref); + if (node != NULL) + return node; + } + + node = transformColumnRefInternal(pstate, cref, list_length(cref->fields), + &crerr); + /* * Now give the PostParseColumnRefHook, if any, a chance. We pass the * translation-so-far so that it can throw an error if it wishes in the @@ -919,13 +945,13 @@ transformColumnRef(ParseState *pstate, ColumnRef *cref) */ if (node == NULL) { - switch (crerr) + switch (crerr.code) { case CRERR_NO_COLUMN: - errorMissingColumn(pstate, relname, colname, cref->location); + errorMissingColumn(pstate, crerr.relname, crerr.colname, cref->location); break; case CRERR_NO_RTE: - errorMissingRTE(pstate, makeRangeVar(nspname, relname, + errorMissingRTE(pstate, makeRangeVar(crerr.nspname, crerr.relname, cref->location)); break; case CRERR_WRONG_DB: -- 2.25.1 [text/x-patch] v6-0009-Enable-non-parenthesized-column-references-in-dot.patch (27.6K, ../../[email protected]/11-v6-0009-Enable-non-parenthesized-column-references-in-dot.patch) download | inline diff: From 240562bc615f1ac04deb5d87c810e33b97809d0c Mon Sep 17 00:00:00 2001 From: Nikita Glukhov <[email protected]> Date: Sun, 2 Apr 2023 01:53:46 +0300 Subject: [PATCH v6 09/11] Enable non-parenthesized column references in dot notation --- src/backend/parser/gram.y | 3 + src/backend/parser/parse_expr.c | 136 +++++++++++++++++++++++++--- src/backend/parser/parse_target.c | 99 +++++++++++++++----- src/include/nodes/parsenodes.h | 4 +- src/include/parser/parse_expr.h | 4 +- src/test/regress/expected/jsonb.out | 84 ++++++++--------- src/test/regress/sql/jsonb.sql | 56 ++++++------ 7 files changed, 275 insertions(+), 111 deletions(-) diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y index a8ee79518ca..29f30d17823 100644 --- a/src/backend/parser/gram.y +++ b/src/backend/parser/gram.y @@ -18715,13 +18715,16 @@ makeColumnRef(char *colname, List *indirection, c->fields = lcons(makeString(colname), indirection); } i->arg = (Node *) c; + i->arg_is_colref = true; /* to distinguish from '(a.col.ref)[...]' case */ return (Node *) i; } else if (IsA(lfirst(l), A_Star)) { +#if 0 /* We only allow '*' at the end of a ColumnRef */ if (lnext(indirection, l) != NULL) parser_yyerror("improper use of \"*\""); +#endif } nfields++; } diff --git a/src/backend/parser/parse_expr.c b/src/backend/parser/parse_expr.c index b82d25c40a4..ea9ca3a6f71 100644 --- a/src/backend/parser/parse_expr.c +++ b/src/backend/parser/parse_expr.c @@ -71,7 +71,8 @@ static Node *transformXmlExpr(ParseState *pstate, XmlExpr *x); static Node *transformXmlSerialize(ParseState *pstate, XmlSerialize *xs); static Node *transformBooleanTest(ParseState *pstate, BooleanTest *b); static Node *transformCurrentOfExpr(ParseState *pstate, CurrentOfExpr *cexpr); -static Node *transformColumnRef(ParseState *pstate, ColumnRef *cref); +static Node *transformColumnRef(ParseState *pstate, ColumnRef *cref, + List **indirection, bool report_error); static Node *transformWholeRowRef(ParseState *pstate, ParseNamespaceItem *nsitem, int sublevels_up, int location); @@ -146,7 +147,7 @@ transformExprRecurse(ParseState *pstate, Node *expr) switch (nodeTag(expr)) { case T_ColumnRef: - result = transformColumnRef(pstate, (ColumnRef *) expr); + result = transformColumnRef(pstate, (ColumnRef *) expr, NULL, true); break; case T_ParamRef: @@ -158,7 +159,8 @@ transformExprRecurse(ParseState *pstate, Node *expr) break; case T_A_Indirection: - result = transformIndirection(pstate, (A_Indirection *) expr, NULL); + result = transformIndirection(pstate, (A_Indirection *) expr, + NULL, NULL, true); break; case T_A_ArrayExpr: @@ -434,20 +436,52 @@ unknown_attribute(ParseState *pstate, Node *relref, const char *attname, Node * transformIndirection(ParseState *pstate, A_Indirection *ind, - bool *trailing_star_expansion) + Node *transformed_arg, + bool *trailing_star_expansion, + bool report_error) { Node *last_srf = pstate->p_last_srf; - Node *result = transformExprRecurse(pstate, ind->arg); + Node *result; + List *indirection = NIL; List *subscripts = NIL; - int location = exprLocation(result); + int location; ListCell *i; + if (transformed_arg) + result = transformed_arg; + else if (IsA(ind->arg, ColumnRef)) + { + /* + * Get the trailing indirection after column name resolution, + * if argument is a non-parenthized column reference. + */ + List **trailing_indirection = ind->arg_is_colref ? + (compat_field_notation ? &indirection : &subscripts) : NULL; + + result = transformColumnRef(pstate, (ColumnRef *) ind->arg, + trailing_indirection, report_error); + if (!result) + return NULL; + } + else + result = transformExprRecurse(pstate, ind->arg); + + location = exprLocation(result); + + /* Prepend trailing indirection of ColumnRef, if any */ + if (indirection) + indirection = list_concat(indirection, ind->indirection); + else + indirection = ind->indirection; + /* * We have to split any field-selection operations apart from - * subscripting. Adjacent A_Indices nodes have to be treated as a single + * subscripting in "compat_field_notation" mode. + * + * Adjacent A_Indices nodes have to be treated as a single * multidimensional subscript operation. */ - foreach(i, ind->indirection) + foreach(i, indirection) { Node *n = lfirst(i); @@ -552,7 +586,12 @@ transformIndirection(ParseState *pstate, A_Indirection *ind, location); if (!newresult) + { + if (!report_error) + return NULL; + unknown_attribute(pstate, result, strVal(n), location); + } /* consume field select */ subscripts = list_delete_first(subscripts); @@ -805,10 +844,13 @@ transformColumnRefInternal(ParseState *pstate, ColumnRef *cref, int nfields, break; } - err->code = crerr; - err->nspname = nspname; - err->relname = relname; - err->colname = colname; + if (err) + { + err->code = crerr; + err->nspname = nspname; + err->relname = relname; + err->colname = colname; + } return node; } @@ -819,11 +861,16 @@ transformColumnRefInternal(ParseState *pstate, ColumnRef *cref, int nfields, * If you find yourself changing this code, see also ExpandColumnRefStar. */ static Node * -transformColumnRef(ParseState *pstate, ColumnRef *cref) +transformColumnRef(ParseState *pstate, ColumnRef *cref, List **p_indirection, + bool report_error) { Node *node = NULL; + List *indirection = NIL; ColRefError crerr; const char *err; + int num_fields; + int max_fields_to_try; + int min_fields_to_try; /* * Check to see if the column reference is in an invalid place within the @@ -913,8 +960,45 @@ transformColumnRef(ParseState *pstate, ColumnRef *cref) return node; } - node = transformColumnRefInternal(pstate, cref, list_length(cref->fields), - &crerr); + /* + * Try to interpret different prefixes of the field list as a + * column reference, starting from the longest prefix. + * 4 is a length of the longset possible 'cat.schema.tab.col' prefix. + * Prefix also can be delimited with the first occurence of '.*'. + * Remaining fields will be treated as indirections. + */ + num_fields = list_length(cref->fields); + max_fields_to_try = Min(4, num_fields); + + for (int j = 1; j < max_fields_to_try; j++) + { + if (IsA(list_nth(cref->fields, j), A_Star)) + { + max_fields_to_try = j + 1; + break; + } + } + + /* + * Don't try 'col.field' case because by the SQL standard, column + * should be the prefixed with table name when used in dot notation. + */ + min_fields_to_try = Min(max_fields_to_try, 2); + + for (int i = max_fields_to_try; i >= min_fields_to_try; i--) + { + /* The first occured error is the most interesting */ + ColRefError *pcrerr = i == max_fields_to_try ? &crerr : NULL; + + node = transformColumnRefInternal(pstate, cref, i, pcrerr); + + if (node) + { + /* Collect trailing indirections */ + indirection = list_copy_tail(cref->fields, i); + break; + } + } /* * Now give the PostParseColumnRefHook, if any, a chance. We pass the @@ -940,11 +1024,33 @@ transformColumnRef(ParseState *pstate, ColumnRef *cref) parser_errposition(pstate, cref->location))); } + if (node) + { + if (p_indirection) + { + /* Return trailing indirection if requested */ + *p_indirection = indirection; + } + else if (indirection) + { + /* Transform trailing indirection passing already transformed node */ + A_Indirection *ind = makeNode(A_Indirection); + + ind->arg = node; + ind->indirection = indirection; + + node = transformIndirection(pstate, ind, node, NULL, false); + } + } + /* * Throw error if no translation found. */ if (node == NULL) { + if (!report_error) + return NULL; + switch (crerr.code) { case CRERR_NO_COLUMN: diff --git a/src/backend/parser/parse_target.c b/src/backend/parser/parse_target.c index 9041e424e1a..4e99725a739 100644 --- a/src/backend/parser/parse_target.c +++ b/src/backend/parser/parse_target.c @@ -45,11 +45,13 @@ static Node *transformAssignmentSubscripts(ParseState *pstate, Node *rhs, CoercionContext ccontext, int location); -static List *ExpandColumnRefStar(ParseState *pstate, ColumnRef *cref, - bool make_target_entry); +static Node *ExpandColumnRefStar(ParseState *pstate, ColumnRef *cref, + bool make_target_entry, ParseExprKind exprKind); static List *ExpandAllTables(ParseState *pstate, int location); static Node *ExpandIndirectionStar(ParseState *pstate, A_Indirection *ind, - bool make_target_entry, ParseExprKind exprKind); + bool make_target_entry, + bool report_error, + ParseExprKind exprKind); static List *ExpandSingleTable(ParseState *pstate, ParseNamespaceItem *nsitem, int sublevels_up, int location, bool make_target_entry); @@ -150,11 +152,18 @@ transformTargetList(ParseState *pstate, List *targetlist, if (IsA(llast(cref->fields), A_Star)) { /* It is something.*, expand into multiple items */ - p_target = list_concat(p_target, - ExpandColumnRefStar(pstate, - cref, - true)); - continue; + Node *columns = ExpandColumnRefStar(pstate, + cref, + true, + exprKind); + + if (!columns || IsA(columns, List)) + { + p_target = list_concat(p_target, (List *) columns); + continue; + } + + transformed = (Node *) columns; } } else if (IsA(res->val, A_Indirection)) @@ -166,9 +175,10 @@ transformTargetList(ParseState *pstate, List *targetlist, Node *columns = ExpandIndirectionStar(pstate, ind, true, + true, exprKind); - if (IsA(columns, List)) + if (!columns || IsA(columns, List)) { /* It is something.*, expand into multiple items */ p_target = list_concat(p_target, (List *) columns); @@ -246,9 +256,14 @@ transformExpressionList(ParseState *pstate, List *exprlist, if (IsA(llast(cref->fields), A_Star)) { /* It is something.*, expand into multiple items */ - result = list_concat(result, - ExpandColumnRefStar(pstate, cref, - false)); + Node *cols = ExpandColumnRefStar(pstate, cref, + false, exprKind); + + if (!cols || IsA(cols, List)) + result = list_concat(result, (List *) cols); + else + result = lappend(result, cols); + continue; } } @@ -259,7 +274,8 @@ transformExpressionList(ParseState *pstate, List *exprlist, if (IsA(llast(ind->indirection), A_Star)) { Node *cols = ExpandIndirectionStar(pstate, ind, - false, exprKind); + false, true, + exprKind); if (!cols || IsA(cols, List)) /* It is something.*, expand into multiple items */ @@ -1135,9 +1151,9 @@ checkInsertTargets(ParseState *pstate, List *cols, List **attrnos) * * The referenced columns are marked as requiring SELECT access. */ -static List * +static Node * ExpandColumnRefStar(ParseState *pstate, ColumnRef *cref, - bool make_target_entry) + bool make_target_entry, ParseExprKind exprKind) { List *fields = cref->fields; int numnames = list_length(fields); @@ -1153,7 +1169,7 @@ ExpandColumnRefStar(ParseState *pstate, ColumnRef *cref, * need not handle the make_target_entry==false case here. */ Assert(make_target_entry); - return ExpandAllTables(pstate, cref->location); + return (Node *) ExpandAllTables(pstate, cref->location); } else { @@ -1192,7 +1208,22 @@ ExpandColumnRefStar(ParseState *pstate, ColumnRef *cref, node = pstate->p_pre_columnref_hook(pstate, cref); if (node != NULL) - return ExpandRowReference(pstate, node, make_target_entry); + return (Node *) ExpandRowReference(pstate, node, make_target_entry); + } + + if (numnames > 2) + { + ListCell *lc; + + numnames = 0; + + foreach(lc, fields) + { + numnames++; + + if (IsA(lfirst(lc), A_Star)) + break; + } } switch (numnames) @@ -1257,7 +1288,7 @@ ExpandColumnRefStar(ParseState *pstate, ColumnRef *cref, errmsg("column reference \"%s\" is ambiguous", NameListToString(cref->fields)), parser_errposition(pstate, cref->location))); - return ExpandRowReference(pstate, node, make_target_entry); + return (Node *) ExpandRowReference(pstate, node, make_target_entry); } } @@ -1266,6 +1297,25 @@ ExpandColumnRefStar(ParseState *pstate, ColumnRef *cref, */ if (nsitem == NULL) { + //if (numnames > 2 || numnames != list_length(fields)) + { + A_Indirection *ind = makeNode(A_Indirection); + ColumnRef *cref2 = makeNode(ColumnRef); + Node *result; + + cref2->fields = list_copy_head(fields, numnames - 1); + cref2->location = cref->location; + + ind->arg = (Node *) cref2; + ind->indirection = list_copy_tail(fields, numnames - 1); + + result = ExpandIndirectionStar(pstate, ind, make_target_entry, + false, exprKind); + + if (result) + return result; + } + switch (crserr) { case CRSERR_NO_RTE: @@ -1292,8 +1342,8 @@ ExpandColumnRefStar(ParseState *pstate, ColumnRef *cref, /* * OK, expand the nsitem into fields. */ - return ExpandSingleTable(pstate, nsitem, levels_up, cref->location, - make_target_entry); + return (Node *) ExpandSingleTable(pstate, nsitem, levels_up, + cref->location, make_target_entry); } } @@ -1362,7 +1412,8 @@ ExpandAllTables(ParseState *pstate, int location) */ static Node * ExpandIndirectionStar(ParseState *pstate, A_Indirection *ind, - bool make_target_entry, ParseExprKind exprKind) + bool make_target_entry, bool report_error, + ParseExprKind exprKind) { Node *expr; ParseExprKind sv_expr_kind; @@ -1374,10 +1425,14 @@ ExpandIndirectionStar(ParseState *pstate, A_Indirection *ind, pstate->p_expr_kind = exprKind; /* Strip off the '*' to create a reference to the rowtype object */ - expr = transformIndirection(pstate, ind, &trailing_star_expansion); + expr = transformIndirection(pstate, ind, NULL, &trailing_star_expansion, + report_error); pstate->p_expr_kind = sv_expr_kind; + if (!expr) + return NULL; + /* '*' was consumed by generic type subscripting */ if (!trailing_star_expansion) return expr; diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h index 0f9462493e3..8f1756004a1 100644 --- a/src/include/nodes/parsenodes.h +++ b/src/include/nodes/parsenodes.h @@ -474,15 +474,13 @@ typedef struct A_Indices * (foo).field1[42][7].field2 * would be represented with a single A_Indirection node having a 4-element * indirection list. - * - * Currently, A_Star must appear only as the last list element --- the grammar - * is responsible for enforcing this! */ typedef struct A_Indirection { NodeTag type; Node *arg; /* the thing being selected from */ List *indirection; /* subscripts and/or field names and/or * */ + bool arg_is_colref; /* arg is non-parenthized column reference */ } A_Indirection; /* diff --git a/src/include/parser/parse_expr.h b/src/include/parser/parse_expr.h index 7f1889e9d84..0aec8d53a66 100644 --- a/src/include/parser/parse_expr.h +++ b/src/include/parser/parse_expr.h @@ -24,6 +24,8 @@ extern Node *transformExpr(ParseState *pstate, Node *expr, ParseExprKind exprKin extern const char *ParseExprKindName(ParseExprKind exprKind); extern Node *transformIndirection(ParseState *pstate, A_Indirection *ind, - bool *trailing_star_expansion); + Node *transformed_arg, + bool *trailing_star_expansion, + bool report_error); #endif /* PARSE_EXPR_H */ diff --git a/src/test/regress/expected/jsonb.out b/src/test/regress/expected/jsonb.out index 95a93a582b1..8d1f53c994e 100644 --- a/src/test/regress/expected/jsonb.out +++ b/src/test/regress/expected/jsonb.out @@ -5824,19 +5824,19 @@ select '12345.0000000000000000000000000000000000000000000005'::jsonb::int8; -- dot notation CREATE TABLE test_jsonb_dot_notation AS SELECT '{"a": [1, 2, {"b": "c"}, {"b": "d", "e": "f", "x": {"y": "yyy", "z": "zzz"}}], "b": [3, 4, {"b": "g", "x": {"y": "YYY", "z": "ZZZ"}}]}'::jsonb jb; -SELECT (jb).* FROM test_jsonb_dot_notation; +SELECT jb.* FROM test_jsonb_dot_notation; jb ------------------------------------------------------------------------------------------------------------------------------ [[1, 2, {"b": "c"}, {"b": "d", "e": "f", "x": {"y": "yyy", "z": "zzz"}}], [3, 4, {"b": "g", "x": {"y": "YYY", "z": "ZZZ"}}]] (1 row) -SELECT (jb).* FROM test_jsonb_dot_notation t; +SELECT jb.* FROM test_jsonb_dot_notation t; jb ------------------------------------------------------------------------------------------------------------------------------ [[1, 2, {"b": "c"}, {"b": "d", "e": "f", "x": {"y": "yyy", "z": "zzz"}}], [3, 4, {"b": "g", "x": {"y": "YYY", "z": "ZZZ"}}]] (1 row) -SELECT (t.jb).* FROM test_jsonb_dot_notation t; +SELECT t.jb.* FROM test_jsonb_dot_notation t; jb ------------------------------------------------------------------------------------------------------------------------------ [[1, 2, {"b": "c"}, {"b": "d", "e": "f", "x": {"y": "yyy", "z": "zzz"}}], [3, 4, {"b": "g", "x": {"y": "YYY", "z": "ZZZ"}}]] @@ -5858,7 +5858,7 @@ SELECT (t.jb).* FROM test_jsonb_dot_notation t; [[1, 2, {"b": "c"}, {"b": "d", "e": "f", "x": {"y": "yyy", "z": "zzz"}}], [3, 4, {"b": "g", "x": {"y": "YYY", "z": "ZZZ"}}]] (1 row) -SELECT (jb).a FROM test_jsonb_dot_notation; +SELECT t.jb.a FROM test_jsonb_dot_notation t; a ------------------------------------------------------------------------- [1, 2, {"b": "c"}, {"b": "d", "e": "f", "x": {"y": "yyy", "z": "zzz"}}] @@ -5870,55 +5870,55 @@ SELECT (jb).a FROM test_jsonb_dot_notation; [1, 2, {"b": "c"}, {"b": "d", "e": "f", "x": {"y": "yyy", "z": "zzz"}}] (1 row) -SELECT (jb).b FROM test_jsonb_dot_notation; +SELECT t.jb.b FROM test_jsonb_dot_notation t; b --------------------------------------------------- [3, 4, {"b": "g", "x": {"y": "YYY", "z": "ZZZ"}}] (1 row) -SELECT (jb).c FROM test_jsonb_dot_notation; +SELECT t.jb.c FROM test_jsonb_dot_notation t; c --- (1 row) -SELECT (jb).a.b FROM test_jsonb_dot_notation; +SELECT t.jb.a.b FROM test_jsonb_dot_notation t; b ------------ ["c", "d"] (1 row) -SELECT (jb).a.* FROM test_jsonb_dot_notation; +SELECT t.jb.a.* FROM test_jsonb_dot_notation t; a ------------------------------------------- ["c", "d", "f", {"y": "yyy", "z": "zzz"}] (1 row) -SELECT (jb).a.*.b FROM test_jsonb_dot_notation; +SELECT t.jb.a.*.b FROM test_jsonb_dot_notation t; b --- (1 row) -SELECT (jb).a.*.x FROM test_jsonb_dot_notation; +SELECT t.jb.a.*.x FROM test_jsonb_dot_notation t; x --- (1 row) -SELECT (jb).a.*.y FROM test_jsonb_dot_notation; +SELECT t.jb.a.*.y FROM test_jsonb_dot_notation t; y ------- "yyy" (1 row) -SELECT (jb).a.*.* FROM test_jsonb_dot_notation; +SELECT t.jb.a.*.* FROM test_jsonb_dot_notation t; a ---------------- ["yyy", "zzz"] (1 row) -SELECT (jb).*.x FROM test_jsonb_dot_notation; +SELECT t.jb.*.x FROM test_jsonb_dot_notation t; x ------------------------------------------------------ [{"y": "yyy", "z": "zzz"}, {"y": "YYY", "z": "ZZZ"}] @@ -5930,7 +5930,7 @@ SELECT (jb).*.x FROM test_jsonb_dot_notation t; [{"y": "yyy", "z": "zzz"}, {"y": "YYY", "z": "ZZZ"}] (1 row) -SELECT ((jb).*).x FROM test_jsonb_dot_notation t; +SELECT (t.jb.*).x FROM test_jsonb_dot_notation t; x --- @@ -5948,86 +5948,86 @@ SELECT ((jb).*)[:].x FROM test_jsonb_dot_notation t; [{"y": "yyy", "z": "zzz"}, {"y": "YYY", "z": "ZZZ"}] (1 row) -SELECT (jb).*.x FROM test_jsonb_dot_notation; +SELECT t.jb.*.x FROM test_jsonb_dot_notation t; x ------------------------------------------------------ [{"y": "yyy", "z": "zzz"}, {"y": "YYY", "z": "ZZZ"}] (1 row) -SELECT (jb).*.x.* FROM test_jsonb_dot_notation; +SELECT t.jb.*.x.* FROM test_jsonb_dot_notation t; x ------------------------------ ["yyy", "zzz", "YYY", "ZZZ"] (1 row) -SELECT (jb).*.x.y FROM test_jsonb_dot_notation; +SELECT t.jb.*.x.y FROM test_jsonb_dot_notation t; y ---------------- ["yyy", "YYY"] (1 row) -SELECT (jb).*.x.z FROM test_jsonb_dot_notation; +SELECT t.jb.*.x.z FROM test_jsonb_dot_notation t; z ---------------- ["zzz", "ZZZ"] (1 row) -SELECT (jb).*.*.y FROM test_jsonb_dot_notation; +SELECT t.jb.*.*.y FROM test_jsonb_dot_notation t; y ---------------- ["yyy", "YYY"] (1 row) -SELECT (jb).*.*.* FROM test_jsonb_dot_notation; +SELECT t.jb.*.*.* FROM test_jsonb_dot_notation t; jb ------------------------------ ["yyy", "zzz", "YYY", "ZZZ"] (1 row) -SELECT (jb).*.*.*.* FROM test_jsonb_dot_notation; +SELECT t.jb.*.*.*.* FROM test_jsonb_dot_notation t; jb ---- (1 row) -SELECT (jb).a.b.c.* FROM test_jsonb_dot_notation; +SELECT t.jb.a.b.c.* FROM test_jsonb_dot_notation t; c --- (1 row) -EXPLAIN (VERBOSE, COSTS OFF) SELECT (jb).* FROM test_jsonb_dot_notation; - QUERY PLAN --------------------------------------------- - Seq Scan on public.test_jsonb_dot_notation +EXPLAIN (VERBOSE, COSTS OFF) SELECT t.jb.* FROM test_jsonb_dot_notation t; + QUERY PLAN +---------------------------------------------- + Seq Scan on public.test_jsonb_dot_notation t Output: jb.* (2 rows) -EXPLAIN (VERBOSE, COSTS OFF) SELECT (jb).a FROM test_jsonb_dot_notation; - QUERY PLAN --------------------------------------------- - Seq Scan on public.test_jsonb_dot_notation +EXPLAIN (VERBOSE, COSTS OFF) SELECT t.jb.a FROM test_jsonb_dot_notation t; + QUERY PLAN +---------------------------------------------- + Seq Scan on public.test_jsonb_dot_notation t Output: jb.a (2 rows) -EXPLAIN (VERBOSE, COSTS OFF) SELECT (jb).a[1] FROM test_jsonb_dot_notation; - QUERY PLAN --------------------------------------------- - Seq Scan on public.test_jsonb_dot_notation +EXPLAIN (VERBOSE, COSTS OFF) SELECT t.jb.a[1] FROM test_jsonb_dot_notation t; + QUERY PLAN +---------------------------------------------- + Seq Scan on public.test_jsonb_dot_notation t Output: jb.a[1] (2 rows) -EXPLAIN (VERBOSE, COSTS OFF) SELECT (jb).a.*['b'] FROM test_jsonb_dot_notation; - QUERY PLAN --------------------------------------------- - Seq Scan on public.test_jsonb_dot_notation +EXPLAIN (VERBOSE, COSTS OFF) SELECT t.jb.a.*['b'] FROM test_jsonb_dot_notation t; + QUERY PLAN +---------------------------------------------- + Seq Scan on public.test_jsonb_dot_notation t Output: jb.a.*['b'::text] (2 rows) -EXPLAIN (VERBOSE, COSTS OFF) SELECT (jb).a.*[1:2]['b'].b FROM test_jsonb_dot_notation; - QUERY PLAN --------------------------------------------- - Seq Scan on public.test_jsonb_dot_notation +EXPLAIN (VERBOSE, COSTS OFF) SELECT t.jb.a.*[1:2]['b'].b FROM test_jsonb_dot_notation t; + QUERY PLAN +---------------------------------------------- + Seq Scan on public.test_jsonb_dot_notation t Output: jb.a.*[1:2][:'b'::text].b (2 rows) diff --git a/src/test/regress/sql/jsonb.sql b/src/test/regress/sql/jsonb.sql index e3b81efa2d0..1fc949aa7a6 100644 --- a/src/test/regress/sql/jsonb.sql +++ b/src/test/regress/sql/jsonb.sql @@ -1580,38 +1580,38 @@ select '12345.0000000000000000000000000000000000000000000005'::jsonb::int8; CREATE TABLE test_jsonb_dot_notation AS SELECT '{"a": [1, 2, {"b": "c"}, {"b": "d", "e": "f", "x": {"y": "yyy", "z": "zzz"}}], "b": [3, 4, {"b": "g", "x": {"y": "YYY", "z": "ZZZ"}}]}'::jsonb jb; -SELECT (jb).* FROM test_jsonb_dot_notation; -SELECT (jb).* FROM test_jsonb_dot_notation t; -SELECT (t.jb).* FROM test_jsonb_dot_notation t; +SELECT jb.* FROM test_jsonb_dot_notation; +SELECT jb.* FROM test_jsonb_dot_notation t; +SELECT t.jb.* FROM test_jsonb_dot_notation t; SELECT (jb).* FROM test_jsonb_dot_notation; SELECT (t.jb).* FROM test_jsonb_dot_notation; SELECT (t.jb).* FROM test_jsonb_dot_notation t; +SELECT t.jb.a FROM test_jsonb_dot_notation t; SELECT (jb).a FROM test_jsonb_dot_notation; -SELECT (jb).a FROM test_jsonb_dot_notation; -SELECT (jb).b FROM test_jsonb_dot_notation; -SELECT (jb).c FROM test_jsonb_dot_notation; -SELECT (jb).a.b FROM test_jsonb_dot_notation; -SELECT (jb).a.* FROM test_jsonb_dot_notation; -SELECT (jb).a.*.b FROM test_jsonb_dot_notation; -SELECT (jb).a.*.x FROM test_jsonb_dot_notation; -SELECT (jb).a.*.y FROM test_jsonb_dot_notation; -SELECT (jb).a.*.* FROM test_jsonb_dot_notation; -SELECT (jb).*.x FROM test_jsonb_dot_notation; +SELECT t.jb.b FROM test_jsonb_dot_notation t; +SELECT t.jb.c FROM test_jsonb_dot_notation t; +SELECT t.jb.a.b FROM test_jsonb_dot_notation t; +SELECT t.jb.a.* FROM test_jsonb_dot_notation t; +SELECT t.jb.a.*.b FROM test_jsonb_dot_notation t; +SELECT t.jb.a.*.x FROM test_jsonb_dot_notation t; +SELECT t.jb.a.*.y FROM test_jsonb_dot_notation t; +SELECT t.jb.a.*.* FROM test_jsonb_dot_notation t; +SELECT t.jb.*.x FROM test_jsonb_dot_notation t; SELECT (jb).*.x FROM test_jsonb_dot_notation t; -SELECT ((jb).*).x FROM test_jsonb_dot_notation t; +SELECT (t.jb.*).x FROM test_jsonb_dot_notation t; SELECT ((jb).*).x FROM test_jsonb_dot_notation t; SELECT ((jb).*)[:].x FROM test_jsonb_dot_notation t; -SELECT (jb).*.x FROM test_jsonb_dot_notation; -SELECT (jb).*.x.* FROM test_jsonb_dot_notation; -SELECT (jb).*.x.y FROM test_jsonb_dot_notation; -SELECT (jb).*.x.z FROM test_jsonb_dot_notation; -SELECT (jb).*.*.y FROM test_jsonb_dot_notation; -SELECT (jb).*.*.* FROM test_jsonb_dot_notation; -SELECT (jb).*.*.*.* FROM test_jsonb_dot_notation; -SELECT (jb).a.b.c.* FROM test_jsonb_dot_notation; - -EXPLAIN (VERBOSE, COSTS OFF) SELECT (jb).* FROM test_jsonb_dot_notation; -EXPLAIN (VERBOSE, COSTS OFF) SELECT (jb).a FROM test_jsonb_dot_notation; -EXPLAIN (VERBOSE, COSTS OFF) SELECT (jb).a[1] FROM test_jsonb_dot_notation; -EXPLAIN (VERBOSE, COSTS OFF) SELECT (jb).a.*['b'] FROM test_jsonb_dot_notation; -EXPLAIN (VERBOSE, COSTS OFF) SELECT (jb).a.*[1:2]['b'].b FROM test_jsonb_dot_notation; +SELECT t.jb.*.x FROM test_jsonb_dot_notation t; +SELECT t.jb.*.x.* FROM test_jsonb_dot_notation t; +SELECT t.jb.*.x.y FROM test_jsonb_dot_notation t; +SELECT t.jb.*.x.z FROM test_jsonb_dot_notation t; +SELECT t.jb.*.*.y FROM test_jsonb_dot_notation t; +SELECT t.jb.*.*.* FROM test_jsonb_dot_notation t; +SELECT t.jb.*.*.*.* FROM test_jsonb_dot_notation t; +SELECT t.jb.a.b.c.* FROM test_jsonb_dot_notation t; + +EXPLAIN (VERBOSE, COSTS OFF) SELECT t.jb.* FROM test_jsonb_dot_notation t; +EXPLAIN (VERBOSE, COSTS OFF) SELECT t.jb.a FROM test_jsonb_dot_notation t; +EXPLAIN (VERBOSE, COSTS OFF) SELECT t.jb.a[1] FROM test_jsonb_dot_notation t; +EXPLAIN (VERBOSE, COSTS OFF) SELECT t.jb.a.*['b'] FROM test_jsonb_dot_notation t; +EXPLAIN (VERBOSE, COSTS OFF) SELECT t.jb.a.*[1:2]['b'].b FROM test_jsonb_dot_notation t; -- 2.25.1 ^ permalink raw reply [nested|flat] 8+ messages in thread
* [PATCH v1] psql: Fix \df tab completion for procedures @ 2025-11-22 14:42 Erik Wienhold <[email protected]> 0 siblings, 0 replies; 8+ messages in thread From: Erik Wienhold @ 2025-11-22 14:42 UTC (permalink / raw) \df also covers procedures since fb421231daa. But the tab completion logic was never changed to align with that. Fix this along the lines of 05e85d35afb. --- src/bin/psql/tab-complete.in.c | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) diff --git a/src/bin/psql/tab-complete.in.c b/src/bin/psql/tab-complete.in.c index 072b98bea08..8f81f5bf035 100644 --- a/src/bin/psql/tab-complete.in.c +++ b/src/bin/psql/tab-complete.in.c @@ -5669,7 +5669,7 @@ match_previous_words(int pattern_id, else if (TailMatchesCS("\\dew*")) COMPLETE_WITH_QUERY(Query_for_list_of_fdws); else if (TailMatchesCS("\\df*")) - COMPLETE_WITH_VERSIONED_SCHEMA_QUERY(Query_for_list_of_functions); + COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_routines); else if (HeadMatchesCS("\\df*")) COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_datatypes); -- 2.55.0 --5rsvev5qaoortkiw-- ^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2025-11-22 14:42 UTC | newest] Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2024-09-26 15:45 Re: SQL:2023 JSON simplified accessor support Alexandra Wang <[email protected]> 2024-09-26 17:16 ` Andrew Dunstan <[email protected]> 2024-09-27 09:49 ` David E. Wheeler <[email protected]> 2024-10-04 12:33 ` jian he <[email protected]> 2024-11-07 21:57 ` Alexandra Wang <[email protected]> 2024-11-14 12:31 ` Peter Eisentraut <[email protected]> 2024-11-20 00:06 ` Nikita Glukhov <[email protected]> 2025-11-22 14:42 [PATCH v1] psql: Fix \df tab completion for procedures Erik Wienhold <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox