I'm working on implementing subscripts for a custom type, but one
usability issue I'm running into is that the default column names for
subscript expressions are rather bad.
The easiest way to show this is with json. Running the following in psql:
CREATE TABLE tj(data jsonb);
INSERT INTO tj values ('{"a": 123, "b": "abc", "c": [123, 456]}');
SELECT data['a'], data['b'], data['c'] FROM tj;
Gives the following output:
data │ data │ data
──────┼───────┼────────────
123 │ "abc" │ [123, 456]
I'd much rather have it output:
a │ b │ c
──────┼───────┼────────────
123 │ "abc" │ [123, 456]
I'm willing to work on improving this, but can see roughly three
different ways of doing so:
1. Change the default alias/eref to be more useful for subscripts
(probably with a GUC to enable/disable this for people depending on
the old names)
2. Add a way for the implementation of a subscript to also provide a
default alias
3. Add a hook for extensions to provide default aliases for arbitrary
expressions
A combination of these is also possible of course. Attached is a
simple POC of option 1.
From 8eac6bb24b4f8f55e0edb05cf856f1e8eb048308 Mon Sep 17 00:00:00 2001
From: Jelte Fennema-Nio <github-t...@jeltef.nl>
Date: Mon, 16 Dec 2024 14:33:46 +0100
Subject: [PATCH v1] Use last string-literal subscript as default column name
---
src/backend/parser/parse_target.c | 30 +++++++++++++++++++++++++++++-
1 file changed, 29 insertions(+), 1 deletion(-)
diff --git a/src/backend/parser/parse_target.c b/src/backend/parser/parse_target.c
index 76bf88c3ca..b1feb038d8 100644
--- a/src/backend/parser/parse_target.c
+++ b/src/backend/parser/parse_target.c
@@ -1784,13 +1784,41 @@ FigureColnameInternal(Node *node, char **name)
char *fname = NULL;
ListCell *l;
- /* find last field name, if any, ignoring "*" and subscripts */
+ /*
+ * find last field name or string literal subscript, if any,
+ * ignoring "*" and non-string-literal subscripts
+ */
foreach(l, ind->indirection)
{
Node *i = lfirst(l);
+ A_Indices *indices;
+ A_Const *constExpr;
if (IsA(i, String))
+ {
fname = strVal(i);
+ continue;
+ }
+
+ if (!IsA(i, A_Indices))
+ continue;
+
+ indices = (A_Indices *) i;
+
+ if (indices->is_slice)
+ continue;
+
+ if (!IsA(indices->uidx, A_Const))
+ continue;
+
+ constExpr = (A_Const *) indices->uidx;
+
+ if (constExpr->isnull)
+ continue;
+
+ if (IsA(&constExpr->val, String))
+ fname = strVal(&constExpr->val);
+
}
if (fname)
{
base-commit: 39240bcad56dc51a7896d04a1e066efcf988b58f
--
2.43.0
