Get definiton scripts of all the (mat)views in a schema at once

All we need is an easy explanation of the problem, so here it is.

I got a schema with several mat views in it, now I need a simple solution (a SELECT statement for example) to get all the CREATE queries.

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

Answer A. With pg_catalog.pg_get_viewdef

DB Fiddle: https://www.db-fiddle.com/f/jBe2vkVn1s92xRYCrwS3h3/3

SELECT 
        c.oid,
        relname,
        n.nspname
        ,format(E'CREATE MATERIALIZED VIEW \nAS\n%s', pg_catalog.pg_get_viewdef(c.oid)) 
    FROM 
        pg_class c
        JOIN pg_catalog.pg_namespace n ON C.relnamespace = n.oid
    WHERE
        relkind='m';
        -- relkind IN ('m', 'v'); - for views too

Answer B. Using ddlx extension

I recommend to look on ddlx extension: https://github.com/lacanoid/pgddl

It gives niceties as scripting dependencies, support of COMMENTs.

Then your question could be answered as:

SELECT 
        c.oid,
        relname,
        n.nspname,
        ddlx_create(c.oid) 
    FROM 
        pg_class c
        JOIN pg_catalog.pg_namespace n ON C.relnamespace = n.oid
    WHERE
        relkind='m'
        -- relkind IN ('m', 'v'); - for views too

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Tags:

Leave a Reply