我需要获取有关所有 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。