Drop or create database from stored procedure in PostgreSQL

dblink, ddl, plpgsql, postgresql, stored-procedures

Solution

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

Problem

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.

Original source