CODEWITHSUNDEEP
postgresql
Wine Too
Wine Too

Reputation: 4655

PostgreSQL, drop tables with query

I have such query to list tables in current database:

SELECT c.relname 
FROM pg_catalog.pg_class c 
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace 
WHERE c.relkind 
IN ('r','') AND n.nspname NOT IN ('pg_catalog', 'pg_toast') AND
pg_catalog.pg_table_is_visible(c.oid)

I am trying to drop all those tables using upper SELECT like subquery:

DROP TABLE IF EXISTS (SELECT c.relname 
FROM pg_catalog.pg_class c 
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace 
WHERE c.relkind 
IN ('r','') AND n.nspname NOT IN ('pg_catalog', 'pg_toast') AND
pg_catalog.pg_table_is_visible(c.oid)) as tname

But that don't work.
How to propery write a query which will drop all tables listed by showed SELECT query?
For now I use DataReader for loop through query result and drop tables by one.
But I think that may go 'at once'.

Upvotes: 3

Views: 6225

Answers (2)

hackmaxed
hackmaxed

Reputation: 41

You can "generate" your DROP statement using a SELECT statement. For example:

SELECT 'DROP TABLE "' + table_name + '"' 
FROM information_schema.tables
WHERE table_name LIKE '[your_prefix_here]%'

(Replace '[your_prefix_here]%' with your conditions and wild cards)

Upvotes: 2

user330315
user330315

Reputation:

You need to use dynamic SQL for this, which in turn can only be used in a procedural language like PL/pgSQL, something like this:

do
$$
declare
   stmt text;
   table_rec record;
begin
   for table_rec in (SELECT c.relname as tname
                     FROM pg_catalog.pg_class c 
                       LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace 
                     WHERE c.relkind IN ('r','') 
                       AND n.nspname NOT IN ('pg_catalog', 'pg_toast') 
                       AND pg_catalog.pg_table_is_visible(c.oid))
   loop
     execute 'drop table '||table_rec.tname||' cascade';
   end loop;
end;
$$

Upvotes: 9

Related Questions