Prepend table name to each column in a result set in SQL? (Postgres specifically)

Question

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.

Answer 1

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] 

Answer 2

To get the VIEW (Daryl's idea) in a single statement use a function or a 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 a command of the form:

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;

Would be less hassle to concatenate legal column names with '_' instead of '.'. But you need to be prepared for non-standard names that require double-quoting (and defend against possible SQL injection) anyway.

You can optionally provide a schema-qualified table name (myschema.person). The schema-name is prefixed in column names automatically if it is outside the current search_path.

For repeated use, you wrap this into a plpgsql function and make the table name a text parameter. All text-to-code conversion is sanitized here to prevent SQL injection. Example with more information here:

• Table name as a PostgreSQL function parameter

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

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

Answer 3

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

As other answers indicated, you can't do this outright. However, you can get table names prepended for columns that share the same name (like foreign keys and last_updated columns). This will give you enough information to tell which table the column came from.

select t1.*, t2.*, t3.*
from table1 t1
join table2 t2 using (fk1_id)
join table3 t3 using (fk2_id)

Column names would show up like this:

pk1_id | t1.last_updated | unique_col_name_from_t1 | t1.fk1_id | t1.fk2_id | t2.fk1_id | t2.last_updated | unique_col_name_from_t2 | t3.fk2_id | t3.last_updated | unique_col_name_from_t3

This won't help in your particular scenario, but answers the general question posted.