Re: Feature suggestion: auto-prefixing SELECT query column names with table/alias names

On Sunday, June 21, 2020, Guy Burgess <guy(at)burgess(dot)co(dot)nz> wrote:
>
> a.id, a.title, b.id, b.title

You are missing some double-quotes there.

Of course, this can be achieved by avoiding the (often frowned-upon) SELECT
> * syntax in the first place and using explicit column names,

Or choose better, distinguishing, column names.

> Could there be an (admittedly non-standard) option to automatically prefix
> table/alias names to columns

The cost/benefit here seems quite unfavorable.

SQL is verbose and SQL doesn't make fields inseparable from the relations
they are attached to. So while "object.name" makes sense in an
object-oriented world it doesn't make as much sense in SQL,
object.object_name does.

At least at the top level of query, when dealing with physical relations at
least, client software has the ability to inspect the result metadata and
for those output columns that are from relations it can lookup related
information by OID. If anything I would restrict manipulation to this
subset of problem space. What I'd want is some way to attach a "human
friendly" label to a column and have the server replace the output column
name for a physical column with that human readable label instead. I'd
still have to alias derived values using "AS" but that's fine.

Tying this back to the original request, if we do focus on top-level tlist
names in most production cases you'd want "Object Name" instead of "
object.name" anyway - so absent something like I describe above you are
just back to writing:
SELECT object.name AS "Object Name"
instead of
SELECT *
and getting something besides "name" back through some behind the scenes
logic.

For ad-hoc queries I'd suggest that the incidence of desiring "SELECT *" is
considerably higher but that the query author has a considerably greater
propensity to know which name is which.

So, in short, this feels like something with a small but real audience but
a non-trivial design, let alone implementation, and has a large degree of
mitigation by adopting technology driven coding and naming standards. For
production outputs it doesn't even do that great a job as final output
names should strive to be human friendly as opposed to the developer
friendly names used in data models.

David J.