List all column names for multi-joined table

Question

I have a slightly more complicated join:

SELECT person.given_name, person.family_name, person.age, person.sex, person.state,
       person.zip, person.street, person.residential_number,
       c1.name as citizen_of_country,
       c2.name as hospitalization_country,
       c3.name as infection_country

FROM patients

    JOIN person on patients.person_id = person.id
    JOIN country c1 on person."citizenOf_country_id" = c1.id
    JOIN country c2 on patients.hospitalized_in_country_id = c2.id
    JOIN country c3 on patients.infected_in_country_id = c3.id;

.. and I'd like to somehow get the name of all columns in this new table. Based on some answers I found, I tried

SELECT DISTINCT column_name FROM information_schema.columns WHERE table_schema = 'public' AND [code];

Where [code] is the block above.. But it didn't work, giving the error "Subquery must return only one column". I'm new to databases so I'm not sure how to handle this correctly.

Answer 1

You can get the information from yourcursor.description. Example:

conn = psycopg2.connect(...)
curs = conn.cursor()
curs.execute("""SELECT person.given_name, ...""")

curs.description

You can find more info under metadata on zetcode.com/python/psycopg2/

Answer 2

I'd like to somehow get the name of all columns in this new table.

You can create a temporary view, which registers the row type. Then query either the information schema like you tried or the system catalog pg_attribute:

CREATE TEMP VIEW tmp_v1 AS SELECT ...;  -- your query here

SELECT attname
FROM   pg_attribute
WHERE  attrelid = 'pg_temp.tmp_v1'::regclass;

Returns the desired list of column names.

Catalog tables or information schema? See:

• Query to return output column names and data types of a query, table or view

The temporary view is only visible inside your current session and dropped at its end. (Or you can drop it manually.)

---

In psql you could use the meta-command \gdesc:

test=# SELECT 1 AS foo \gdesc
 Column |  Type
--------+---------
 foo    | integer

The manual:

\gdesc

Shows the description (that is, the column names and data types) of the result of the current query buffer. The query is not actually executed; however, if it contains some type of syntax error, that error will be reported in the normal way.

If the current query buffer is empty, the most recently sent query is described instead.

So you can even execute a bare \gdesc after running the query to get a "table description" for the executed query.