PostgreSQL中物化视图的列名和数据类型？ [英] Column names and data types for materialized views in PostgreSQL?

问题描述

对于常规表和视图，我可以通过运行以下查询来查看其数据类型：

For general tables and views, I can see their data type by running the following query:

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

但是，似乎没有任何有关物化的信息视图出现在这里。

However it does not seem that any information about materialized views appear here.

我能够通过运行以下命令来获取物化视图的列列表：

I am able to get a list of columns for a materialized view by running:

    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

我无法弄清楚是否可以确定底层的列/数据类型是什么。

But, I have not been able to figure out if I can determine what the underlying column/data type is.

是否可以查看此信息？

推荐答案

我认为您非常亲密。最后一步是加入 pg_type ：

I think you're very close. Last step would be to join with pg_type:

join pg_catalog.pg_type as tp on tp.typelem = a.atttypid

字段 tp.typname 将具有数据类型。尽管似乎必须添加一个过滤器才能删除 line 数据类型，但无论如何：

The field tp.typname would have the datatype. Although it seems a filter must be added to remove line datatypes, whatever that is:

cast(tp.typanalyze as text) = 'array_typanalyze'

我不完全了解底层数据模型，因此请在下面使用我的解决方案。无论如何，基于您的贡献，我最终得到了以下查询，该查询使用名称空间（例如，架构）和关系（例如，物化视图）名称获取列数据类型：

I don't fully understand the underlying data model, so use my solution below with a grain of salt. Anyway, based on your contribution I ended up with the following query which gets column datatypes using namespace (e.g., schema) and relation (e.g., materialized view) name:

select 
    ns.nspname as schema_name, 
    cls.relname as table_name, 
    attr.attname as column_name,
    trim(leading '_' from 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.typelem = attr.atttypid
where 
    ns.nspname = 'your_schema' and
    cls.relname = 'your_materialized_view' and 
    not attr.attisdropped and 
    cast(tp.typanalyze as text) = 'array_typanalyze' and 
    attr.attnum > 0
order by 
    attr.attnum

您必须更改您的模式 和您的物化视图 。

这篇关于PostgreSQL中物化视图的列名和数据类型？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！