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
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
答案 A. 与
pg_catalog.pg_get_viewdef
DB小提琴:https ://www.db-fiddle.com/f/jBe2vkVn1s92xRYCrwS3h3/3
答案 B. 使用
ddlx
扩展我建议查看
ddlx
扩展名:https ://github.com/lacanoid/pgddl它提供了脚本依赖、评论支持等细节。
那么你的问题可以这样回答: