2

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?

  • Put each view into a single SQL script, create one script to drop all views and another one calling each "view script" to re-create the views – a_horse_with_no_name Mar 28 '15 at 13:05
  • @a_horse_with_no_name that gets me one step closer. Then the question is how to manage the day to day changes. It seems less than optimal to drop and create all views every time the rails migration runs due to some change. Is that how people handle this? – mhenrixon Mar 28 '15 at 14:31
5

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;
  • 3
    Dropping production tables with cascade seems like a terrible idea? – mhenrixon Mar 28 '15 at 14:36
  • The query seems to do the trick though, going to see if that order works. – mhenrixon Mar 28 '15 at 14:37
  • Best. Catalog. Query. Ever. – Doctor Eval Apr 19 '18 at 2:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.