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?
<mailto:jtocci(at)tlcusa(dot)com>
-------------------------------------------
justin tocci
Fort Wayne, IN