Running sql scripts with interdependencies

Question

I have a project utilizing a postgres database in the backend and I'm working to create a Dockerfile which can automatically set up a local development instance of the db. The database includes a large number of functions, and these have historically been stored in context-appropriate sql files e.g. users.sql, companies.sql. This has been fine as whenever changes are made, I can simply execute the relevant sql files which REPLACE all Views and drop/recreate all of the Functions.

However, when trying to run these scripts on a fresh postgres instance (after CREATEing all tables), they fail because most of the views/functions reference other views functions which are not defined yet.

I have begun investigating if there is a particular order I can run the scripts in to avoid this issue, but since they weren't designed with this purpose in mind it may be impossible, and there is a large number of entities at play so this is most likely a non-trivial task.

Is there any way to achieve this apart from restructuring the scripts?

Answer 1

You can write a simple recursive query that starts with all views that don't depend on others and recursively adds the views that depend on these. Then output the view definition for these views in the correct order, and you have got your script:

WITH RECURSIVE viewids AS (
   /* all views that don't depend on other views */
   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 (
            /* depends on a view */
            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 (
            /* depends on an extension */
            SELECT 1
            FROM pg_depend
            WHERE objid = t.oid
              AND classid = 'pg_class'::regclass
              AND refclassid = 'pg_extension'::regclass
              AND deptype = 'e'
         )
UNION ALL
   /* all views that depend on these views */
   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'::regclass                            
     AND d.refclassid = 'pg_class'::regclass
     AND d.deptype = 'n'
     AND d.refobjsubid <> 0
)
/* order the views by level, eliminating duplicates */
SELECT format('CREATE VIEW %s AS%s',
              oid::regclass,
              pg_get_viewdef(oid::regclass))
FROM viewids
GROUP BY oid
ORDER BY max(level);