PostgreSQL中材料化视图的列名和数据类型是什么？

Question

对于一般的表格和视图，我可以通过运行以下查询来查看它们的数据类型：

select data_type from information_schema.columns
where .....

然而，并没有任何关于物化视图的信息出现在这里。

通过运行以下命令，我可以获取一个物化视图的列列表：

    SELECT
      a.attname as column_name
  FROM
      pg_catalog.pg_attribute a
      INNER JOIN
       (SELECT c.oid,
          n.nspname,
          c.relname
        FROM pg_catalog.pg_class c
             LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
        WHERE c.relname ~ ('^(materializedview)$')
          AND pg_catalog.pg_table_is_visible(c.oid)
        ORDER BY 2, 3) b
      ON a.attrelid = b.oid
      INNER JOIN
       (SELECT
            a.attrelid,
            max(a.attnum) as max_attnum
        FROM pg_catalog.pg_attribute a
        WHERE a.attnum > 0
          AND NOT a.attisdropped
        GROUP BY a.attrelid) e
      ON a.attrelid=e.attrelid
  WHERE a.attnum > 0
    AND NOT a.attisdropped
  ORDER BY a.attnum

但是，我还没有找到一种方法来确定底层列/数据类型。

有没有一种方法可以查看这些信息？

- 1969877

Answer 1

我认为你很接近了。最后一步是与pg_type连接:

join pg_catalog.pg_type as tp on tp.oid = attr.atttypid

字段tp.typname将具有数据类型。

以下查询使用命名空间（例如模式）和关系（例如材料化视图）名称获取列数据类型：

select 
    attr.attnum,
    ns.nspname as schema_name, 
    cls.relname as table_name, 
    attr.attname as column_name,
    tp.typname as datatype
from pg_catalog.pg_attribute as attr
join pg_catalog.pg_class as cls on cls.oid = attr.attrelid
join pg_catalog.pg_namespace as ns on ns.oid = cls.relnamespace
join pg_catalog.pg_type as tp on tp.oid = attr.atttypid
where 
    ns.nspname = 'your_schema'
    and cls.relname = 'your_materialized_view'
    and attr.attnum >= 1
order by 
    attr.attnum

您需要更改'your_schema'和'your_materialized_view'。

- André C. Andersen

---

@andré-c-andersen，为什么你要通过 typelem 加入 pg_catalog.pg_type？根据文档，该字段仅适用于类似数组的类型。我认为应该使用 pg_catalog.pg_type.oid。 - Hubbitus

@Hubbitus 那只是无知。我根据你的建议更新了查询。现在看起来更加清晰，而且似乎运行正常。感谢。 - André C. Andersen