Feature suggestion: auto-prefixing SELECT query column names withtable/alias names

Поиск
Список
Период
Сортировка
От Guy Burgess
Тема Feature suggestion: auto-prefixing SELECT query column names withtable/alias names
Дата
Msg-id 7fe94be5-14c0-7117-c64a-d5633fe4f8bc@burgess.co.nz
обсуждение исходный текст
Ответы Re: Feature suggestion: auto-prefixing SELECT query column nameswith table/alias names  (Laurenz Albe <laurenz.albe@cybertec.at>)
Re: Feature suggestion: auto-prefixing SELECT query column names withtable/alias names  ("David G. Johnston" <david.g.johnston@gmail.com>)
Список pgsql-general
Hello,

I've seen some situations where it would be very handy if PostgreSQL 
could automatically prefix column names in a SELECT query with their 
table names (or its alias).   So for two tables, a & b, each with 
columns "id" and "title":

     SELECT * FROM a , b;

instead of returning ambiguously-named columns:

     id, title, id, title

would return:

     a.id, a.title, b.id, b.title

Of course, this can be achieved by avoiding the (often frowned-upon) 
SELECT * syntax in the first place and using explicit column names, but 
that makes otherwise short queries much longer and harder to maintain.  
Also this doesn't only apply to SELECT * queries: if tables a & b had 
more columns, but I wanted just the "title" column of each, to avoid 
ambiguous column names or having to use column position indexes, I have 
to give explicit aliases to those columns (which could be anything, but 
in practice is often the table prefix as already written in the select 
(SELECT a.title AS "a.title". b.title as "b.title" etc).

Could there be an (admittedly non-standard) option to automatically 
prefix table/alias names to columns? From searching, it seems a number 
of people would find this useful[1].  Perhaps something like:   SELECT 
#* FROM a, b    -->   a.id, a.title, b.id, d.title.  This would allow 
use per-table:   SELECT a.id, #b.*   -->   id, b.id, b.title.   Or per 
column  E.g:   SELECT a.id, #a.title, #b.title   -->   id, a.title. 
b.title.   Or alternatively it could be specified in the FROM clause as 
to which tables have their columns prefixed.

I have no idea if this is viable/sensible/anathema, especially given the 
understandable reluctance to stray from the SQL standard, but thought 
I'd ask, especially as it seems like something others would find useful too.

Thanks,

Guy

[1]

https://stackoverflow.com/questions/329931/sql-select-join-is-it-possible-to-prefix-all-columns-as-prefix

https://stackoverflow.com/questions/13153344/in-a-join-how-to-prefix-all-column-names-with-the-table-it-came-from

https://stackoverflow.com/questions/53947657/how-to-display-table-name-as-prefix-to-column-name-in-sql-result

https://stackoverflow.com/questions/33364392/sql-join-prefix-fields-with-table-name




В списке pgsql-general по дате отправления:

Предыдущее
От: raf
Дата:
Сообщение: Re: Hiding a GUC from SQL
Следующее
От: Laurenz Albe
Дата:
Сообщение: Re: Feature suggestion: auto-prefixing SELECT query column nameswith table/alias names