I'm looking for a SELECT that I could add a WHERE clause to and get all the CREATE (and seperately, DROP) statements that I need to rebuild dependent objects before I make changes to my tables.
For instance, I have a column datatype I want to change in a table. In order to do this I need to drop all the 'first tier' views that depend on it and then re-create them when I'm done, plus I need the 'second tier' views and rules that that depend on the 'first tier' views etc... So if I could just run a couply SELECTs and save the results as .sql scripts I could run, I'd be all set. Rarely the CREATE script would need to be edited to run without error, but it still would be a huge time saver.
So essentially I'm looking for two SELECTs (DROP and CREATE) that would hand me the scripts to do this. (Or is there another way?)
Just so you don't think I didn't try to figure this out already, I've tried to work with the pg_depend table and this is what I have so far. Read on to see where I'm stuck.
Select distinct pg_depend.objid,
desc1.relname AS desca,
pg_depend.refobjid,
desc2.relname AS descb,
pg_depend.deptype
from pg_depend left join (select pg_class.oid, pg_class.relname from pg_class UNION select pg_rewrite.ev_class, pg_rewrite.rulename from pg_rewrite UNION select pg_rewrite.oid, pg_rewrite.rulename from pg_rewrite UNION select pg_namespace.oid, pg_namespace.nspname from pg_namespace UNION select pg_constraint.oid, pg_constraint.conname from pg_constraint UNION select pg_type.oid, pg_type.typname from pg_type UNION select pg_attrdef.oid, pg_attrdef.adsrc from pg_attrdef) desc1 on pg_depend.objid = desc1.oid
left join (select pg_class.oid, pg_class.relname from pg_class UNION select pg_rewrite.ev_class, pg_rewrite.rulename from pg_rewrite UNION select pg_rewrite.oid, pg_rewrite.rulename from pg_rewrite UNION select pg_namespace.oid, pg_namespace.nspname from pg_namespace UNION select pg_constraint.oid, pg_constraint.conname from pg_constraint UNION select pg_type.oid, pg_type.typname from pg_type UNION select pg_attrdef.oid, pg_attrdef.adsrc from pg_attrdef) desc2 on pg_depend.refobjid = desc2.oid
where deptype <> 'p'
This gives me the table I'm looking for in column DescB, hooked to its dependent rules in column DescA, but I haven't been able to get dependent views to show up at all. In fact views that depend on views don't show up either. Does pg_depend not have records for dependent views? If not, how is it done?
------------------------------------------- justin tocci
Fort Wayne, IN
