Postgres: Drop Table according to similar schema name

Question

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).

Answer 1

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 $$;