Posgresql: delete all custom defined tables, functions, procedures and enums on current database



  • I am trying to create a procedure to delete all custom defined elements in the current database I'm operating with. That includes:

    • tables
    • procedures
    • functions
    • enums

    I don't want to drop schema and recreate it: the idea is just to delete these elements.

    This is an attempt I did taking code from https://dba.stackexchange.com/questions/122742/how-to-drop-all-of-my-functions-in-postgresql and https://stackoverflow.com/questions/3327312/how-can-i-drop-all-the-tables-in-a-postgresql-database .

    drop procedure if exists reset_db;
    

    create procedure reset_db() language plpgsql AS $$

    begin
    -- Drop all functions and procedures
    declare _sql text;
    begin
    select into _sql
    string_agg(format('DROP %s %s;'
    , case prokind
    when 'f' then 'FUNCTION'
    when 'p' then 'PROCEDURE'
    end
    , oid::regprocedure)
    , E'\n')
    from pg_proc
    where pronamespace = current_schema()::regnamespace
    ;

    if _sql is not null then
        raise notice '%', _sql;  -- debug / check first
        execute _sql;         -- uncomment payload once you are sure
    else
        raise notice 'No fuctions found in schema %', quote_ident(_schema);
    end if;
    

    end;

    -- Drop custom types. Don't know how to iterate here to automate it.
    drop type LevelEnum cascade;
    drop type StatusEnum cascade;

    -- Drop all tables
    declare
    r record;
    begin
    for r in (select tablename from pg_tables where schemaname = current_schema()) loop
    execute 'drop table if exists ' || quote_ident(r.tablename) || ' cascade';
    end loop;
    end;

    end;
    $$;

    Currently this seems to delete other functions apart of the ones I defined for my current DB. Also I don't know how to loop through the custom defined types as the enums to delete them.

    Any help will be welcome.



  • The only good way to do that is to have all the objects you want to get rid of be owned by a certain role. Then you can run

    DROP OWNED BY owning_role;
    

Log in to reply
 


Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2