I would like to be able to drop a schema with all of its objects, but
don't want to accidentally drop objects that are in other schemas. If
there are such objects I'd prefer the drop to fail. If I use DROP SCHEMA
FOO CASCADE I run the risk of dropping objects from other schemas that
depend on objects in FOO (e.g. a table that uses a domain/user defined
datatype from FOO). I don't particularly like such cross-schema
dependencies but they are easy to create acidentally and I don't want to
blow out a big table.
Any thoughts on this? "Talk to the people who wrote the SQL standard" is
one response, of course, but what do you all think is a reasonable
behavior here? Anyone else see this as an issue? Does this warrant an PG
extension to SQL? How do other DBMSs do it?
As an immediate solution can anyone share a comprehensive query to
INFORMATION_SCHEMA/PG_CATALOG that can be used to check for any
cross-schema dependencies and halt before issuing a DROP with a CASCADE?
TIA,
George