Re: Improving default column names/aliases of subscript text expressions

Jelte Fennema-Nio <postgres(at)jeltef(dot)nl> writes:
> 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 dunno, this seems to be putting an undue amount of emphasis on
one very specific usage pattern. Why should it matter whether
the subscripts are string literals or not? What will happen when
ruleutils decides to dump those expressions with the implicit
cast to text being less implicit?

regression=# create view v1 as
SELECT data['a'] FROM tj;
CREATE VIEW
regression=# \d+ v1
View "public.v1"
Column | Type | Collation | Nullable | Default | Storage | Description
--------+-------+-----------+----------+---------+----------+-------------
data | jsonb | | | | extended |
View definition:
SELECT data['a'::text] AS data
FROM tj;

I'm also wondering how such a rule interacts with string literals
that don't get resolved as text:

regression=# create table t2 (data int[]);
CREATE TABLE
regression=# insert into t2 values(array[1,2,3,4]);
INSERT 0 1
regression=# select data['2'], data[3] from t2;
data | data
------+------
2 | 3
(1 row)

> 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)

This would be a GUC that changes query semantics, which is a
thing we've learned the hard way is evil. (It would not be
any less evil in one of the other implementation approaches.)
I think that to do something like this, we'd have to make a
considered decision that the new way is so much better than
the old that it's okay to break some people's queries.
I don't say that bar is unreachable, but it seems high.

One way to lower the bar would be to make the change affect
fewer cases, like maybe only JSON subscripts. That points
towards your idea of making the subscript implementation
responsible. However, if memory serves we pick the column
aliases before doing parse analysis, which'd make it hard
to know which data type is involved. I do not really recall
why it's done that way or whether it'd be practical to change.

regards, tom lane