How to get column attributes from a query using PostgreSQL?

Question

I need to get the fields attributes from a query, like in this question:How to get column attributes query from table name using PostgreSQL? but for a query, is there a way of doing this?

Answer 1

Assuming you're using psycopg2 as your database driver, then the cursor.description field is what you want:

import pprint
import psycopg2
conn = psycopg2.connect('');
curs = conn.cursor()
curs.execute("SELECT 1 as col1, 2 as col2, 'text' as colblah");
pprint.pprint(curs.description)

produces:

(Column(name='col1', type_code=23, display_size=None, internal_size=4, precision=None, scale=None, null_ok=None),
 Column(name='col2', type_code=23, display_size=None, internal_size=4, precision=None, scale=None, null_ok=None),
 Column(name='colblah', type_code=705, display_size=None, internal_size=-2, precision=None, scale=None, null_ok=None))

The type codes are PostgreSQL's internal object IDs.

For more detail see the psycopg2 manual, which explains how to turn the type oids into type names, among other things.