0

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 ;

etc

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)

1

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 $$
Begin 
   execute format('drop database %I ', 'bak_20180404_2014_12'); 
End
$$ ;

You can combine that with a loop:

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

Edit

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
  • and ... is there a way past DROP DATABASE cannot be executed from a function or multi-command string ? (on pg9.6) – Chris F Carroll Apr 6 '18 at 17:18
  • @ChrisFCarroll: ah, right I always forget that. If you are using psql there is an easy workaround. – a_horse_with_no_name Apr 6 '18 at 17:44
  • Alas. But not a big deal. Thanks. – Chris F Carroll Apr 6 '18 at 17:48
  • 1
    @ChrisFCarroll: see my edit – a_horse_with_no_name Apr 6 '18 at 17:48
  • Perfect! thankyou. I think the \gexec is what I was looking for. – Chris F Carroll Apr 6 '18 at 18:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.