Skip to content

Instantly share code, notes, and snippets.

@xlucasdemelo
Created February 21, 2017 19:43
Show Gist options
  • Save xlucasdemelo/51d8d4a94e9a402daadf0579b0144a76 to your computer and use it in GitHub Desktop.
Save xlucasdemelo/51d8d4a94e9a402daadf0579b0144a76 to your computer and use it in GitHub Desktop.
Drop all schemas from a database postgresql
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();
@cjj1120
Copy link

cjj1120 commented Nov 6, 2023

changing the EXECUTE statement works for me
EXECUTE format('DROP SCHEMA "%s" CASCADE', rec.nspname);

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment