Drop or create database from stored procedure in PostgreSQL

Question

I have a function that looks like this:

BEGIN
  DROP DATABASE IF EXISTS db_1;
END;

I'm getting the following error:

ERROR: DROP DATABASE cannot be executed from a function or multi-command string.

Is it not possible to drop a database from a stored procedure in PostgreSQL? I'm using plpgsql.

Answer 1

The error message is just a s clear as the manual on this:

DROP DATABASE cannot be executed inside a transaction block.

A plgpsql function is surrounded by a transaction block automatically. The long and the short of it: you cannot do that - directly. Is there a particular reason you can't just call the DDL command?

DROP database $mydb;

You can circumvent these restrictions with the additional module dblink as @Igor suggested. You need to install it once per database - the one where you call dblink functions, not the (other) one you execute commands in.
Allows you to write a function using dblink_exec() like this:

CREATE OR REPLACE FUNCTION f_drop_db(text)
  RETURNS text LANGUAGE sql AS
$func$
SELECT dblink_exec('port=5432 dbname=postgres'
                  ,'DROP DATABASE ' || quote_ident($1))
$func$;

quote_ident() prevents possible SQL injection.

Call:

SELECT f_drop_db('mydb');

On success you see:

DROP DATABASE

The connection string could even point to the same db your session runs in. The command runs outside a transaction block, which has two consequences:

• It cannot be rolled back.
• It allows you to call DROP DATABASE "by way of a proxy" from within a function.

You could create a FOREIGN DATA WRAPPER and a FOREIGN SERVER to store a connection and simplify the call:

CREATE FOREIGN DATA WRAPPER postgresql VALIDATOR postgresql_fdw_validator;

CREATE SERVER your_fdw_name_here FOREIGN DATA WRAPPER postgresql
OPTIONS (hostaddr '12.34.56.78', port '5432', dbname 'postgres');

Using default maintenance db postgres, which would be obvious choice. But any db is possible.

Simplified function making use of that:

CREATE OR REPLACE FUNCTION f_drop_db(text)
  RETURNS text LANGUAGE sql AS
$func$
SELECT dblink_exec('your_fdw_name_here', 'DROP DATABASE ' || quote_ident($1))
$func$;