PostgreSQL – How to Get DDL of a Materialized View
To retrieve the Data Definition Language (DDL) for a materialized view in PostgreSQL, you can use the pg_get_viewdef
function, which is a built-in function for getting the definition (DDL) of various database objects, including materialized views. Here’s the syntax to retrieve the DDL for a materialized view:
SELECT pg_get_viewdef('your_materialized_view_name'::regclass, true);
where your_materialized_view_name
is the name of the materialized view you want to get the DDL for.
Here’s an example:
SELECT pg_get_viewdef('employee_report'::regclass, true);
This query will return the SQL definition of the employee_report materialized view, including the SQL query used to create the materialized view.