Re: Improving inferred query column names

Hi,

On 2023-02-22 16:38:51 -0500, Tom Lane wrote:
> Vladimir Churyukin <vladimir(at)churyukin(dot)com> writes:
> > It doesn't need to be perfect, but it needs to be consistent. So far you
> > proposed a rule to replace () with _. What is the plan for expressions, how
> > to convert them to names (with deduplication I guess?, because there could
> > be 2 similar expressions mapped to the same name potentially).
>
> I do not think we need to do anything for arbitrary expressions.

Exactly. It's not like they have a useful name today. Nor are they unique.

> The proposal so far was just to handle a function call wrapped
> around something else by converting to the function name followed
> by whatever we'd emit for the something else.

Just to showcase that better, what I think we're discussing is changing:

SELECT sum(relpages), sum(reltuples), 1+1 FROM pg_class;
┌──────┬────────┬──────────┐
│ sum │ sum │ ?column? │
├──────┼────────┼──────────┤
│ 2774 │ 257896 │ 2 │
└──────┴────────┴──────────┘
(1 row)

to

SELECT sum(relpages), sum(reltuples), 1+1 FROM pg_class;
┌──────────────┬───────────────┬──────────┐
│ sum_relpages │ sum_reltuples │ ?column? │
├──────────────┼───────────────┼──────────┤
│ 2774 │ 257896 │ 2 │
└──────────────┴───────────────┴──────────┘
(1 row)

> You cannot realistically
> handle, say, operator expressions without emitting names that will
> require quoting, which doesn't seem attractive.

Well, it doesn't require much to be better than "?column?", which already
requires quoting...

We could just do something like printing <left>_<funcname>_<right>. So
something like avg(reltuples / relpages) would end up as
avg_reltuples_float48div_relpages.

Whether that's worth it, or whether column name lengths would be too painful,
IDK.

> And no, deduplication isn't on the table at all here.

+1

Greetings,

Andres Freund