Prepare a table name for each column in a result set in SQL? (Postgres specifically)

Question

How can I get the label of each column in the result set to add a name if its a table?

I want this to happen both with queries to individual tables, and with 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 because I use a database driver that does not provide metadata informing me of the database column where the result set got its data from. I am trying to write general code to handle a result set.

I would like to know how to do this in SQL in general, or at least in Postgres specifically.

SQLite had such a feature, although I can see that now it is inexplicably outdated. SQLite has two pragma settings: full_column_names and short_column_names .

Answer 1

I know this question is a bit outdated, but maybe someone will stumble upon an answer and it will help them.

The right way to do what you are looking for is to create and use a View. Yes, it will be a little tedious to parse all these new column names as aliases, but if there are many columns here, you can use the trick to use 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;'; 

It works, it gives:

 ?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 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] 

Answer 2

To get the VIEW ( Daryl idea ) in a single expression , use a function or DO command with EXECUTE :

 DO $do$ BEGIN EXECUTE ( SELECT format( 'CREATE TEMP VIEW people AS SELECT %s FROM %I' , string_agg(format('%I AS %I', attname, attrelid::regclass || '.' || attname), ', ') , attrelid::regclass) FROM pg_attribute WHERE attrelid = 'person'::regclass -- supply source table name once AND attnum > 0 AND NOT attisdropped GROUP BY attrelid ); END $do$; 

This immediately executes the form command:

 CREATE OR REPLACE VIEW people AS SELECT person_id AS "person.person_id" , first_name AS "person.first_name" , last_name AS "person.last_name" FROM person; 

It would be less hassle to concatenate the column names in the field "_" instead of ".". But you should be prepared for non-standard names, which in any case require double quotation (and protection against a possible SQL injection).

You can also specify the name of the table with the table ( myschema.person ). The schema name is automatically added to the column names if it is outside the current search_path .

For repeated use, you transfer this to the plpgsql function and create the table name a text . All text to code conversions are sanitized here to prevent SQL injection. An example with more information is here:

• Table name as parameter of PostgreSQL function

And you can use the new to_regclass() in Postgres 9.4 +:

• How to check if a table exists in a given schema