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.
>Solution :
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.