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 !
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');
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';
ERROR : more than one row returned by a subquery used as an expression.