Column names from query

Question

I'm trying to get make a query to return a list of the column names from a query result in postgresql

e.g.

SELECT column_name 
  FROM ( 
        SELECT table1.* 
          FROM table1 
               LEFT JOIN table2 
                         ON table1.id = table2.tbl1_id
        )

is this possible

I DO NOT WANT THE COLUMNS FROM A SINGULAR TABLE!!! so please dont tell me to do

SELECT column_name
  FROM information_schema.columns
 WHERE table_name = 'table1'

Answer 1

I don't know why you would ever need to do this, but it is possible using some of the JSON functions introduced in 9.3.

SELECT json_object_keys(row_to_json(t)) FROM
 (SELECT * FROM table1
  LEFT JOIN table2 ON table1.id = table2.tbl1_id LIMIT 1) t;

This will give you the name of every column returned for a single row. Without the LIMIT you would get the columns repeated for every row returned. If you wanted to see the values returned as well you can get more complex:

WITH t as
  (SELECT * FROM table1
   LEFT JOIN table2 ON table1.id = table2.tbl1_id LIMIT 1)
SELECT json_data.key, json_data.value
FROM t, json_each_text(row_to_json(t)) AS json_data;

Both these queries will return all the columns even if they are named the same. If all you want is a list of unique column names, you can utilize hstore:

CREATE EXTENSION hstore; --Create the extension if you need it.

SELECT akeys(hstore(t)) as array_of_columns
FROM
(SELECT * FROM table1
 LEFT JOIN table2 ON table1.id = table2.tbl1id LIMIT 1) t;