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?