Postgres 元數據：獲取所有 MATERIALIZED VIEW 列的列表，具有數據類型，包括數組列

我需要獲取有關所有 MATERIALIZED VIEW 列的元數據，包括它們的數據類型。我可以用這個查詢來做到這一點：

SELECT 

trim(leading '_' from pg_type.typname) as data_type,
pg_namespace.nspname as schema_name, 
pg_namespace.nspname as udt_name, 
attnum AS ordinal_position,
pg_attribute.attname as column_name,
pg_class.relname as matview_name, 
'-------' AS spacer,
*


from pg_catalog.pg_attribute
join pg_catalog.pg_class ON pg_class.oid = pg_attribute.attrelid
join pg_catalog.pg_namespace ON pg_namespace.oid = pg_class.relnamespace
join pg_catalog.pg_type ON pg_type.typelem = pg_attribute.atttypid
where 
   pg_class.relkind = 'm' AND
   not pg_attribute.attisdropped and 
   cast(pg_type.typanalyze as text) = 'array_typanalyze' and 
   pg_attribute.attnum > 0
order by 
   pg_attribute.attnum

但是它沒有列出任何數組列，因為它們不存在於pg_catalog.pg_type表中（這是我從中獲取數據類型的表）。

我如何也可以獲得數組列的元數據（包括類型）？

我正在使用 postgres 13.3。

您可以使用以下format_type()功能讓您的生活更輕鬆：

SELECT format_type(att.atttypid, att.atttypmod) as data_type, 
      nsp.nspname as schema_name, 
      att.attnum AS ordinal_position,
      att.attname as column_name,
      mv.relname as matview_name
from pg_catalog.pg_attribute att
 join pg_catalog.pg_class mv ON mv.oid = att.attrelid
 join pg_catalog.pg_namespace nsp ON nsp.oid = mv.relnamespace
where mv.relkind = 'm' 
 AND not att.attisdropped 
 and att.attnum > 0
order by att.attnum

引用自：https://dba.stackexchange.com/questions/295242