How can I drop and create postgresql views in dependency order?

Question

I found the following https://stackoverflow.com/a/9712051/61249 which is good but still too much manual labor for me. I need to recreate the views in the right order how would I go about doing that?

@dbenhur talks about the following:

To be more complete, one could figure out how to query which views depend on the table(es) you're modifying and use that query instead f enumerating view names. Gonna need to understand pg_rewrite | pg_rule to do that, I think

I am unsure what that means exactly but I'll tell you want I need and you tell me if it is possible.

I work on a rails application where I've tried to maintain the views and functions as part of the rails migrations unfortunately this became a real mess and I dumped our views and functions into separate files for each and one of those. There are two ways forward as I see it. I could either use a single file for these which makes it cumbersome but I guess the order of the DDL would be managed by pg_dump.

OR I could when I dump the views and functions also query and create a dependency tree that I later use for recreating them. The reason is that it has become too complex to do manually for just changing the name of a column in a view.

How do others maintain their DDL? Anyone ever done anything like what I want to do?

Answer 1

You may take a look on "drop table base cascade". after that you have to recreace the structure on your own. I think its the easyer way. Or you can extend the answer, you found with some search in the pg_depend table. For the "clean" method, please check the docs, as a dirty way here is a query:

WITH RECURSIVE t AS (
  SELECT c.oid,c.relname, 0 as level FROM pg_class c where relname='base' UNION ALL
  SELECT c.oid,c.relname, a.level+1 FROM t a
  JOIN pg_depend d ON d.refobjid=a.oid
  JOIN pg_rewrite w ON w.oid= d.objid AND w.ev_class!=a.oid
  JOIN pg_class c ON c.oid=w.ev_class
) SELECT distinct * FROM t order by level;