Query the definition of a materialized view in Postgres

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

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.