postgresql - Column data types for materialized views? -

for general tables , views, can see data type running following query:

select data_type information_schema.columns ..... 

however not seem information materialized views appear here.

i able list of columns materialized view running:

    select       a.attname column_name         pg_catalog.pg_attribute       inner join        (select c.oid,           n.nspname,           c.relname         pg_catalog.pg_class c              left join pg_catalog.pg_namespace n on n.oid = c.relnamespace         c.relname ~ ('^(materializedview)$')           , pg_catalog.pg_table_is_visible(c.oid)         order 2, 3) b       on a.attrelid = b.oid       inner join        (select             a.attrelid,             max(a.attnum) max_attnum         pg_catalog.pg_attribute         a.attnum > 0           , not a.attisdropped         group a.attrelid) e       on a.attrelid=e.attrelid   a.attnum > 0     , not a.attisdropped   order a.attnum 

but, have not been able figure out if can determine underlying column/data type is.

is there way view information?

i think you're close. last step join pg_type:

join pg_catalog.pg_type tp on tp.typelem = a.atttypid 

the field tp.typname have datatype. although seems filter must added remove line datatypes, whatever is:

cast(tp.typanalyze text) = 'array_typanalyze' 

i don't understand underlying data model, use solution below grain of salt. anyway, based on contribution ended following query gets column datatypes using namespace (e.g., schema) , relation (e.g., materialized view) name:

select      ns.nspname schema_name,      cls.relname table_name,      attr.attname column_name,     trim(leading '_' tp.typname) datatype pg_catalog.pg_attribute attr join pg_catalog.pg_class cls on cls.oid = attr.attrelid join pg_catalog.pg_namespace ns on ns.oid = cls.relnamespace join pg_catalog.pg_type tp on tp.typelem = attr.atttypid      ns.nspname = 'your_schema' ,     cls.relname = 'your_materialized_view' ,      not attr.attisdropped ,      cast(tp.typanalyze text) = 'array_typanalyze' ,      attr.attnum > 0 order      attr.attnum 

you have change 'your_schema'and 'your_materialized_view'.