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