17

I'm wondering how to query the definition of a materialized view in Postgres. For reference, what I hoped to do is very similar to what you can do for a regular view:

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

which gives you the following columns:

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

Is this possible for materialized views?

From my research so far, it appears that materialized views are deliberately excluded from information_schema, because

The information_schema can only show objects that exist in the SQL standard.

(http://www.postgresql.org/message-id/3794.1412980686@sss.pgh.pa.us)

Since they appear to being entirely excluded from information_schema, I'm not sure how to go about this, but what I'd like to do is twofold:

  1. Query whether a particular materialized view exists. (So far the only way I've found to do this is try creating a mat view with the same name and see if it blows up.)
  2. And then query the definition of the materialized view (similar to the view_definition column on information_schema.views).
23

Looks like 9.3 and up you can do:

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

More info found here: https://stackoverflow.com/questions/29297296/postgres-see-query-used-to-create-materialized-view

12

Turns out this wasn't as complicated as I thought! (With just a little knowledge of pg_catalog...)

Part 1: Query whether a materialized view exists:

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';

Nice and easy.

Part 2: Query the definition of a materialized view:

In order to come up with a query to get the definition of the mat view, I first had to look up the definition of the information_schema.views view by running:

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

Then I copied out the query and changed c.relkind = 'v'::"char" to c.relkind = 'm'::"char" in order to get mat views (instead of regular views). See the full query here: http://pastebin.com/p60xwfes

At this point you could pretty easily add AND c.relname = 'some_mat_view' and run it to get the definition of some_mat_view.

But you'd still have to do this all over again next time you want to look up the definition of a mat view...

Bonus: Create a view to make this easier

I opted to create a new view to make it easier to look up mat view definitions in the future. I basically just added CREATE VIEW materialized_views AS to the beginning of the query linked above to create the new view, and now I can query it like so:

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

Much better!

I can also use this view to easily query whether a materialized view exists by changing * to count(*) > 0.

Disclaimer: I don't know it the other columns in the query results are entirely correct, since materialized views are fundamentally different from standard views (I think they're right). But this does at least query the table_schema, table_name and view_definition correctly.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.