Início / dba / Perguntas / 102620
Accepted
Sean the Bean
Asked: 2015-05-28 13:35:12 +0800 CST

Consultar a definição de uma visão materializada no Postgres

  • 772

Eu estou querendo saber como consultar a definição de uma visão materializada no Postgres. Para referência, o que eu esperava fazer é muito semelhante ao que você pode fazer para uma visualização regular:

SELECT * FROM information_schema.views WHERE table_name = 'some_view';

que lhe dá as seguintes colunas:

table_catalog
table_schema
table_name
view_definition
check_option
is_updatable
is_insertable_into
is_trigger_updatable
is_trigger_deletable
is_trigger_insertable_into

Isso é possível para visualizações materializadas?

Da minha pesquisa até agora, parece que as visões materializadas são deliberadamente excluídas do information_schema, porque

O information_schema só pode mostrar objetos que existem no padrão SQL.

( http://www.postgresql.org/message-id/[email protected] )

Como eles parecem estar totalmente excluídos do information_schema, não tenho certeza de como fazer isso, mas o que eu gostaria de fazer é duplo:

  1. Consultar se existe uma visão materializada específica. (Até agora, a única maneira que encontrei de fazer isso é tentar criar uma visualização de esteira com o mesmo nome e ver se ela explode.)
  2. E, em seguida, consulte a definição da visualização materializada (semelhante à view_definitioncoluna em information_schema.views).
postgresql materialized-view

4 respostas

  1. Parece 9.3 ou superior, você pode fazer:

    select * from pg_matviews;
select * from pg_matviews where matviewname = 'view_name';

    Mais informações encontradas aqui: https://stackoverflow.com/questions/29297296/postgres-see-query-used-to-create-materialized-view

    • 39
  2. Best Answer

    Acontece que isso não era tão complicado quanto eu pensava! (Com apenas um pouco de conhecimento de pg_catalog...)

    Parte 1: Consultar se existe uma visualização materializada:

    SELECT count(*) > 0
FROM pg_catalog.pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind = 'm'
AND n.nspname = 'some_schema'
AND c.relname = 'some_mat_view';

    Legal e fácil.

    Parte 2: Consulte a definição de uma visualização materializada:

    Para criar uma consulta para obter a definição da exibição de mat, primeiro tive que procurar a definição da information_schema.viewsexibição executando:

    SELECT view_definition
FROM information_schema.views
WHERE table_schema = 'information_schema'
AND table_name = 'views';

    Em seguida, copiei a consulta e alterei c.relkind = 'v'::"char"para c.relkind = 'm'::"char"obter visualizações de mat (em vez de visualizações regulares). Veja a consulta completa aqui: http://pastebin.com/p60xwfes

    Neste ponto, você pode facilmente adicionar AND c.relname = 'some_mat_view'e executá-lo para obter a definição de some_mat_view.

    Mas você ainda terá que fazer tudo de novo na próxima vez que quiser procurar a definição de uma visualização de esteira ...

    Bônus: crie uma visualização para facilitar isso

    Optei por criar uma nova exibição para facilitar a pesquisa de definições de exibição de esteira no futuro. Basicamente, acabei de adicionar CREATE VIEW materialized_views ASao início da consulta vinculada acima para criar a nova visualização e agora posso consultá-la assim:

    SELECT *
FROM materialized_views
WHERE table_schema = 'some_schema'
AND table_name = 'some_mat_view';

    Muito melhor!

    Também posso usar essa exibição para consultar facilmente se existe uma exibição materializada alterando *para count(*) > 0.

    Isenção de responsabilidade : não sei se as outras colunas nos resultados da consulta estão totalmente corretas, pois as visualizações materializadas são fundamentalmente diferentes das visualizações padrão ( acho que estão certas). Mas isso pelo menos consulta o table_schema, table_namee view_definitioncorretamente.

    • 15

  3. A desvantagem com as outras respostas aqui é que você apenas obtém a definição SQL, enquanto na maioria dos casos você está interessado nas colunas reais e pode manipulá-las como texto. A seguir está minha resposta de uma pergunta semelhante , que inclui nomes de colunas e tipos de dados:

    Não posso dizer que entendo completamente o modelo de dados subjacente, então use minha solução abaixo com um grão de sal:

    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

    Você tem que mudar 'your_schema'e 'your_materialized_view'.

    • 2

  4. Postgres 14.5

    SELECT schemaname, matviewname, definition FROM <database_name>.pg_catalog.pg_matviews;
    • -2

relate perguntas