|
Hi, I have a deployment routine to deal with PG’s requirement
of having no dependencies in order to alter a table. The routine basically navigates the dependency tree and saves
off the ddl to a table and drops the dependent (always a view) along the way. Once the alter table is completed, the views are
recreated and permissions regranted. Unfortunately, I’m missing some of the
dependencies. I’ve spent hours trying to understand how to do this,
but considering I still don’t have it correct. I thought I should
ask the experts. The views are complex views based on other views. They
do show up in the PGAdmin dependency list. This is my sql SELECT (nsc.nspname::text || '.'::text) || cl.relname::text
AS refobj_name, (rwns.nspname::text || '.'::text) || rwcl.relname::text AS
depobj_name , pg_get_viewdef((rwns.nspname::text || '.'::text) ||
rwcl.relname::text, true) AS depobj_ddl , array_to_string(rwcl.relacl, ','::text) AS depobj_acl FROM pg_namespace nsc JOIN pg_class cl ON cl.relnamespace = nsc.oid
-- JOIN pg_depend dep ON dep.refobjid = cl.oid LEFT JOIN pg_rewrite rw ON dep.objid = rw.oid LEFT JOIN pg_class rwcl ON rwcl.oid =
rw.ev_class LEFT JOIN pg_namespace rwns ON
rwcl.relnamespace = rwns.oid WHERE rw.rulename = '_RETURN'::name AND
((nsc.nspname::text || '.'::text) || cl.relname::text) <>
((rwns.nspname::text || '.'::text) || rwcl.relname::text) GROUP BY (nsc.nspname::text || '.'::text) ||
cl.relname::text, (rwns.nspname::text || '.'::text) ||
rwcl.relname::text, pg_get_viewdef((rwns.nspname::text ||
'.'::text) ||
rwcl.relname::text, true), array_to_string(rwcl.relacl, ','::text); Thoughts? I query with the name of the base table I’m trying to change. Doug
Little Sr.
Data Warehouse Architect | Enterprise Data Management | Orbitz Worldwide 500
W. Madison, Suite 1000 Chicago IL 60661| Office 312.260.2588 | Fax
312.894.5164 | Cell 847-997-5741 |
