CODEWITHSUNDEEP
postgresqlcascadesql-drop
RicardoAFS
RicardoAFS

Reputation: 3

Postgres: Drop Table according to similar schema name

I want to drop a group of tables from a lot of schemas. All the schemas that I want to drop tables starts name with "pm_". I would like to drop this group of tables from all schemas that starts with "pm_".

Something like that:

DROP TABLE IF EXISTS pm_%.tableName CASCADE;

(where % would be the name continuation).

Upvotes: 0

Views: 247

Answers (1)

maniek
maniek

Reputation: 7307

Use plpgsql, like this:

do $$ 
    declare schemaname text; 
    begin  
    for schemaname in select schema_name 
                     from information_schema.schemata 
                     where schema_name like 'pm\_%' 
                     loop 
        execute 'drop table if exists ' || quote_ident(schemaname) || '.tablename'; 
    end loop;  
end $$;

Upvotes: 1

Related Questions