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
[email protected]<mailto:[email protected]>
[cid:[email protected]] orbitz.com<http://www.orbitz.com/> |
ebookers.com<http://www.ebookers.com/> |
hotelclub.com<http://www.hotelclub.com/> |
cheaptickets.com<http://www.cheaptickets.com/> |
ratestogo.com<http://www.ratestogo.com/> |
asiahotels.com<http://www.asiahotels.com/>
<<inline: image001.jpg>>
