1

I need to delete a schema in my PostgreSQL database. I would like to make sure that the schema is not used in another schema (for instance : this schema is used for a view in another one). Is there a way to list all the dependencies in the rest of the database ?

I have tried DROP SCHEMA mySchema ; but the given list of dependencies is partial...

Many thanks for help !

1

You can use pg_depend table to resolve that : 

select objid::regclass 
from pg_depend 
where refobjid = (select oid
                  from pg_namespace 
                  where nspname='mySchema');
  • Thanks. I just tried with a schema known to be referenced in oustside views. The query result just returns a list of tables but I don't know from which schema they are from... – wiltomap Oct 3 '14 at 8:46
  • Ok, sorry, understood. – slardiere Oct 3 '14 at 9:44
1

here's a quick and ugly query that make the job :

with nsp_oid as (
  select oid from pg_namespace
    where nspname='mySchema'
), 
dep_oid as (
  select p.objid as oid
    from pg_depend p
    join nsp_oid n
      on refobjid = n.oid 
), 
rew_oid as(
  select p.objid as oid
    from pg_depend p
    join dep_oid d
      on p.refobjid = d.oid
    where p.classid::regclass::text = 'pg_rewrite'
)
select distinct refobjid::regclass
  from pg_depend p
  join  rew_oid r 
    on ( r.oid in ( p.refobjid, p.objid ))
  where deptype='n';
  • Ugly indeed ;-) ! A closing bracket is missing. But where do I put my schema name ? Thanks for that. – wiltomap Oct 3 '14 at 9:59
  • in nspname, in the first CTE ( nspname='mySchema' ) – slardiere Oct 3 '14 at 10:13
  • It's ugly since your formating leaves a lot to be desired. – Kamil Gosciminski Oct 3 '14 at 10:58
  • I get this : ERROR : more than one row returned by a subquery used as an expression. – wiltomap Oct 3 '14 at 11:20
  • Yeah, maybe subqueries needs to be join, actually – slardiere Oct 3 '14 at 11:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.