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/[email protected])
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:
- 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.)
- And then query the definition of the materialized view (similar to the
view_definition
column oninformation_schema.views
).
SELECT to_regclass('some_schema.some_mat_view')
- if found, it doesn't have to be a MV, though. Details: stackoverflow.com/questions/20582500/…