I need to be able to get the DDL from a materialized view. We are wanting to track how often a MV gets updated/changed, and I want to be able to automate this process. I just need the DDL for the MV, not the column names/select statement.

CodePudding user response：

You can get the view definition like this:

SELECT pg_get_viewdef('schemaname.mvname');

To get the complete DDL statement, prepend the following:

CREATE MATERIALIZED VIEW schemaname.mvname AS

The only way to track changes in a materialized view definition would be to create an event trigger on ddl_command_end.