主页 / dba / 问题 / 295242
Accepted
LaVache
Asked: 2021-07-05 01:30:12 +0800 CST

Postgres 元数据：获取所有 MATERIALIZED VIEW 列的列表，具有数据类型，包括数组列

  • 772

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

postgresql datatypes

1 个回答

  1. Best Answer

    您可以使用以下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
    • 1

相关问题