Creating views in dependency order

[bjeanes]

It's possible to query for all views along with any views they depend on:

WITH views AS (
  SELECT viewname
       , definition
  FROM pg_views
  WHERE schemaname = 'public'
)

SELECT viewname
     , children
     , definition
FROM views
  LEFT JOIN (
    SELECT vtu.view_name
         , array_agg(vtu.table_name::text)
    FROM information_schema.view_table_usage vtu
    WHERE vtu.view_schema  = 'public'
      AND vtu.table_schema = 'public'
      AND vtu.table_name IN (SELECT viewname FROM views)
    GROUP BY 1
  ) deps(viewname, children) USING (viewname)
;

(this only considers public schema, but so does existing implementation).

Then, instead of ordering by viewname as is done now, a topological sort could be straight-forwardly provide an iteration order for views that would avoid having to make multiple pgdiff passes.

I expended some effort this evening trying to do the topological sort in the query (which would make it a drop-in replacement) but didn't get anywhere fruitful. It seems possible but it's either too late or my SQL-fu is not strong enough.

Instead, the tsort could be done in Go. Or, someone else could possibly step in and amend the query above to do it in Postgres.

[joncrlsn]

Interesting... I'm working on version 0.9.3 right now. The sorting is all done in Go now because I ran into trouble with collation differences between some databases which made for unreliable comparisons. We'll see if I can make the time to do something with topological sorting.