How can I get the label of each column in a result set to prepend the name if its table?
I want this to happen for queries on single tables as well as joins.
Example:
SELECT first_name, last_name FROM person;
I want the results to be:
| person.first_name | person.last_name |
|-------------------|------------------|
| Wendy | Melvoin |
| Lisa | Coleman |
I could use "AS" to define an alias for each column, but that would be tedious. I want this to happen automatically.
SELECT first_name AS person.first_name, last_name AS person.last_name FROM person;
The reason for my question is that I am using a database driver that does not provide the meta-data informing me the database column from where the result set got its data. I am trying to write generic code to handle the result set.
I would like to know how to do this in SQL generally, or at least in Postgres specifically.
SQLite had such a feature, though I see it is now inexplicably deprecated. SQLite has two pragma settings: full_column_names & short_column_names.
I know this question is a bit old, but perhaps someone will stumble over the answer and it will help them out.
The proper way to do what you're looking for is to create and use a View. Yes, it will be a bit tedious one-time to type out all those new column names as aliases, but if there are a lot of columns here's a trick you can use to leverage the PostgreSQL metadata to write out the text of the view:
select 'CREATE OR REPLACE VIEW people AS SELECT ' ||
(select string_agg(column_name || ' AS person_' || column_name, ', ')
from information_schema.columns
where table_name = 'person'
group by table_name) ||
' FROM person;';
running this yields:
?column?
-------------------------------------------------------------------------------------------------------------
CREATE OR REPLACE VIEW people AS SELECT last_name AS person_last_name, first_name AS person_first_name FROM person;
1 record(s) selected [Fetch MetaData: 0/ms] [Fetch Data: 0/ms]
[Executed: 4/21/12 2:05:21 PM EDT ] [Execution: 9/ms]
you can then copy and execute the results and voila:
select * from people;
person_last_name person_first_name
------------------- --------------------
Melvoin Wendy
Coleman Lisa
2 record(s) selected [Fetch MetaData: 1/ms] [Fetch Data: 0/ms]