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.

Similar Posts