sqlpostgresqlview

Running sql scripts with interdependencies


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?


Solution

  • 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);