Postgres anonymous block execution of generated dynamic sql to drop databases

Posted on

Question :

I have backup databases I wish to drop. I can generate a list :

select 'drop database ' || datname || ' ;' from pg_database where datname like 'bak_%'

getting me

drop database bak_20180101 ;
drop database bak_20180102 ;


but when I try

Do $$Begin execute 'drop database $1 ;' Using  'bak_20180404_2014_12' ; End$$ ;

that gets me syntax error at or near "$1"

and when I try

Do $$Begin execute 'drop database bak_20180404_2014_12' ; End$$ ;

that gets me DROP DATABASE cannot be executed from a function or multi-command string

Is there a simple way to drop a generated list of databases?
(and what was wrong with my Execute Using)

Answer :

You can’t pass identifiers as parameters, you need to create a dynamic string. The best way to do that is to use format()

Do $$
   execute format('drop database %I ', 'bak_20180404_2014_12'); 
$$ ;

You can combine that with a loop:

  l_rec record;
  for l_rec in select datname from pg_database where datname like 'bak_%' 
     execute format('drop database %I ', l_rec.datname); 
  end loop;


I always forget that you can’t drop a database like that. But in psql you can easily run the result of a query as a script:

postgres=> select 'drop database '||datname||';' from pg_database 
postgres-> gexec

Note the missing ; at the end of the select statement. When you terminate the query with gexec instead of ; the query’s result will be executed as a script.

Another option is to simply spool the output to a file, so that you can inspect the drop statements before running them:

postgres=> t
Tuples only is on.
postgres=> o drop.sql
postgres=> select 'drop database '||datname||';' from pg_database;
postgres=> o
postgres=> i drop.sql

Leave a Reply

Your email address will not be published. Required fields are marked *