Re: remaining sql/json patches
Alvaro Herrera <alvherre@alvh.no-ip.org>
Commits
GET /api/v1/messages/:b64id/commits
the thread's linked commits as JSON, with link sources.
API reference →
-
SQL/JSON: Various improvements to SQL/JSON query function docs
- ce416fadb4b6 17.0 landed
- 42de72fa7b80 18.0 landed
-
SQL/JSON: Fix some obsolete comments.
- 290a6d800d90 17.0 landed
- 7768b6569de9 16.4 landed
- 3a8a1f3254b2 18.0 landed
-
SQL/JSON: Fix issues with DEFAULT .. ON ERROR / EMPTY
- c0fc0751862d 17.0 landed
-
JSON_TABLE: Add support for NESTED paths and columns
- bb766cde63b4 17.0 landed
-
Fix JsonExpr deparsing to emit QUOTES and WRAPPER correctly
- f6a2529920cf 17.0 landed
-
Fix typo introduced in 6185c9737
- 2f6e78b0619a 17.0 landed
-
Add basic JSON_TABLE() functionality
- de3600452b61 17.0 landed
-
Avoid splitting errmsg string to span multiple lines
- 085e759e9da7 17.0 landed
-
Add SQL/JSON query functions
- 6185c9737cf4 17.0 landed
-
Implement various jsonpath methods
- 66ea94e8e606 17.0 cited
-
Add soft error handling to some expression nodes
- aaaf9449ec6b 17.0 landed
- 7fbc75b26ed8 17.0 landed
-
Adjust populate_record_field() to handle errors softly
- 1edb3b491bee 17.0 landed
-
Refactor code used by jsonpath executor to fetch variables
- faa2b953ba3b 17.0 landed
-
Test EXPLAIN (FORMAT JSON) ... XMLTABLE
- 752533d40fd5 17.0 landed
-
Simplify productions for FORMAT JSON [ ENCODING name ]
- d3fe6e90bab5 17.0 landed
-
Add trailing commas to enum definitions
- 611806cd726f 17.0 cited
-
doc: add missing <returnvalue> and whitespace
- e055b6be7ebb 17.0 landed
-
Add more SQL/JSON constructor functions
- 03734a7fed7d 17.0 landed
-
Rename a nonterminal used in SQL/JSON grammar
- 254ac5a7c31f 17.0 landed
-
Some refactoring to export json(b) conversion functions
- b22391a2ff7b 17.0 landed
-
Don't include CaseTestExpr in JsonValueExpr.formatted_expr
- 66a9003e2e3e 16.0 landed
- b6e1157e7d33 17.0 landed
-
Code review for commit b6e1157e7d
- 7c7412cae3ea 17.0 landed
-
Pass constructName to transformJsonValueExpr()
- 7825a1b01e40 16.0 landed
- 785480c9533d 17.0 landed
-
Unify JSON categorize type API and export for external use
- 3c152a27b063 17.0 landed
-
Make some indentation in gram.y consistent
- 5edf438eeb00 17.0 landed
- 01f1f789df56 16.0 landed
-
Allow most keywords to be used as column labels without requiring AS.
- 06a7c3154f5b 14.0 cited
-
Reduce size of backend scanner's tables.
- 7f380c59f800 13.0 cited
-
Use perfect hashing, instead of binary search, for keyword lookup.
- c64d0cd5ce24 12.0 cited
About 0002:
I think we should just drop it. Look at the changes it produces in the
plans for aliases XMLTABLE:
> @@ -1556,7 +1556,7 @@ SELECT f.* FROM xmldata, LATERAL xmltable('/ROWS/ROW[COUNTRY_NAME="Japan" or COU
> Output: f."COUNTRY_NAME", f."REGION_ID"
> -> Seq Scan on public.xmldata
> Output: xmldata.data
> - -> Table Function Scan on "xmltable" f
> + -> Table Function Scan on "XMLTABLE" f
> Output: f."COUNTRY_NAME", f."REGION_ID"
> Table Function Call: XMLTABLE(('/ROWS/ROW[COUNTRY_NAME="Japan" or COUNTRY_NAME="India"]'::text) PASSING (xmldata.data) COLUMNS "COUNTRY_NAME" text, "REGION_ID" integer)
> Filter: (f."COUNTRY_NAME" = 'Japan'::text)
Here in text-format EXPLAIN, we already have the alias next to the
"xmltable" moniker, when an alias is present. This matches the
query itself as well as the labels used in the "Output:" display.
If an alias is not present, then this says just 'Table Function Scan on "xmltable"'
and the rest of the plans refers to this as "xmltable", so it's also
fine.
> @@ -1591,7 +1591,7 @@ SELECT f.* FROM xmldata, LATERAL xmltable('/ROWS/ROW[COUNTRY_NAME="Japan" or COU
> "Parent Relationship": "Inner", +
> "Parallel Aware": false, +
> "Async Capable": false, +
> - "Table Function Name": "xmltable", +
> + "Table Function Name": "XMLTABLE", +
> "Alias": "f", +
> "Output": ["f.\"COUNTRY_NAME\"", "f.\"REGION_ID\""], +
> "Table Function Call": "XMLTABLE(('/ROWS/ROW[COUNTRY_NAME=\"Japan\" or COUNTRY_NAME=\"India\"]'::text) PASSING (xmldata.data) COLUMNS \"COUNTRY_NAME\" text, \"REGION_ID\" integer)",+
This is the JSON-format explain. Notice that the "Alias" member already
shows the alias "f", so the only thing this change is doing is
uppercasing the "xmltable" to "XMLTABLE". We're not really achieving
anything here.
I think the only salvageable piece from this, **if anything**, is making
the "xmltable" literal string into uppercase. That might bring a little
clarity to the fact that this is a keyword and not a user-introduced
name.
In your 0003 I think this would only have relevance in this query,
+-- JSON_TABLE() with alias
+EXPLAIN (COSTS OFF, VERBOSE)
+SELECT * FROM
+ JSON_TABLE(
+ jsonb 'null', 'lax $[*]' PASSING 1 + 2 AS a, json '"foo"' AS "b c"
+ COLUMNS (
+ id FOR ORDINALITY,
+ "int" int PATH '$',
+ "text" text PATH '$'
+ )) json_table_func;
+ QUERY PLAN
+--------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------
+ Table Function Scan on "JSON_TABLE" json_table_func
+ Output: id, "int", text
+ Table Function Call: JSON_TABLE('null'::jsonb, '$[*]' AS json_table_path_0 PASSING 3 AS a, '"foo"'::jsonb AS "b c" COLUMNS (id FOR ORDINALITY, "int" integer PATH '$', text text PATH '$') PLAN (json_table_path_0))
+(3 rows)
and I'm curious to see what this would output if this was to be run
without the 0002 patch. If I understand things correctly, the alias
would be displayed anyway, meaning 0002 doesn't get us anything.
Please do add a test with EXPLAIN (FORMAT JSON) in 0003.
Thanks
--
Álvaro Herrera PostgreSQL Developer — https://www.EnterpriseDB.com/
"La vida es para el que se aventura"