具有相互依赖关系的运行SQL脚本

您可以编写一个简单的递归查询，该查询以不依赖于其他视图的所有视图开头，然后以递归方式添加依赖于这些视图的视图。然后以正确的顺序输出这些视图的视图定义，您已经获得了脚本：

WITH RECURSIVE viewids AS (      SELECt t.oid, 1 as level   FROM pg_class t      JOIN pg_rewrite AS r ON r.ev_class = t.oid   WHERe r.rulename = '_RETURN'     AND t.relkind = 'v'     AND t.relnamespace NOT IN ('pg_catalog'::regnamespace,          'information_schema'::regnamespace,          'pg_toast'::regnamespace)     AND NOT EXISTS (  SELECt 1 FROM pg_depend AS d    JOIN pg_class AS t2 ON d.refobjid = t2.oid WHERe d.objid = r.oid   AND d.classid = 'pg_rewrite'::regclass   AND d.refclassid = 'pg_class'::regclass   AND d.deptype = 'n'   AND d.refobjsubid <> 0   AND t2.relkind = 'v'         )     AND NOT EXISTS (  SELECt 1 FROM pg_depend WHERe objid = t.oid   AND classid = 'pg_class'::regclass   AND refclassid = 'pg_extension'::regclass   AND deptype = 'e'         )UNIOn ALL      SELECt t.oid, viewids.level + 1   FROM pg_class AS t      JOIN pg_rewrite AS r ON r.ev_class = t.oid      JOIN pg_depend AS d ON d.objid = r.oid      JOIN viewids ON viewids.oid = d.refobjid   WHERe t.relkind = 'v'     AND r.rulename = '_RETURN'     AND d.classid = 'pg_rewrite'::regclassAND d.refclassid = 'pg_class'::regclass     AND d.deptype = 'n'     AND d.refobjsubid <> 0)SELECt format('CREATE VIEW %s AS%s',   oid::regclass,   pg_get_viewdef(oid::regclass))FROM viewidsGROUP BY oidORDER BY max(level);