如何查看物化视图的信息

Question

我有一个工具，使用以下方法来内省表格的列：

select column_name, data_type from information_schema.columns
        where table_name=%s

我如何将此扩展以内省物化视图的列？

- shaunc

Answer 1

您的查询存在一些不足之处/需要改进的空间：

• 数据库中表名不是唯一的，你需要缩小到特定的模式，否则可能会得到令人惊讶/误导/完全不正确的结果。
  将（可选）模式限定的表名转换为regclass更加有效/方便...请参见下文。

• 将类型转换为regtype会给出通用类型名称而不是内部名称。但那仍然只是基本类型。
  使用系统目录信息函数format_type()来获取包括修饰符的精确类型名称。

• 通过以上改进，您无需加入其他表即可完成操作。只需pg_attribute。

• 已删除的列仍然存在于目录中，直到表被完全清理。你需要将它们排除掉。

SELECT attname, atttypid::regtype AS base_type
              , format_type(atttypid, atttypmod) AS full_type
FROM   pg_attribute
WHERE  attrelid = 'myschema.mytable'::regclass
AND    attnum > 0
AND    NOT attisdropped;  -- no dead columns

作为旁注：信息模式中的视图仅适用于标准合规性和可移植性（很少起作用）。如果您不打算切换RDBMS，请使用目录表，这些表速度更快-而且显然更完整。

- Erwin Brandstetter

---

感谢您的整理和注释！ - shaunc

Answer 2

似乎Postgres 9.3在information_schema中没有包含物化视图。(参见http://postgresql.1045698.n5.nabble.com/Re-Materialized-views-WIP-patch-td5740513i40.html进行讨论。)

以下内容可用于内省：

select attname, typname 
from pg_attribute a 
join pg_class c on a.attrelid = c.oid 
join pg_type t on a.atttypid = t.oid
where relname = %s and attnum >= 1;

attnum >= 1这个条件可以抑制系统列。我猜是为了让类型名称保持pg_specific，但对于我的目的来说足够好了。

- shaunc