Created
February 21, 2017 19:43
-
-
Save xlucasdemelo/51d8d4a94e9a402daadf0579b0144a76 to your computer and use it in GitHub Desktop.
Drop all schemas from a database postgresql
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
SET search_path = _global, pg_catalog; | |
CREATE OR REPLACE FUNCTION drop_all () | |
RETURNS VOID AS | |
$$ | |
DECLARE rec RECORD; | |
BEGIN | |
-- Get all the schemas | |
FOR rec IN | |
select distinct schemaname | |
from pg_catalog.pg_tables | |
-- You can exclude the schema which you don't want to drop by adding another condition here | |
where schemaname not like 'pg_catalog' | |
LOOP | |
EXECUTE 'DROP SCHEMA ' || rec.schemaname || ' CASCADE'; | |
END LOOP; | |
RETURN; | |
END; | |
$$ LANGUAGE plpgsql; | |
select drop_all(); |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
changing the EXECUTE statement works for me
EXECUTE format('DROP SCHEMA "%s" CASCADE', rec.nspname);