在PostgreSQL中通过存储过程删除或创建数据库

Question

我有一个函数看起来像这样：

BEGIN
  DROP DATABASE IF EXISTS db_1;
END;

我遇到了以下错误:

ERROR: DROP DATABASE不能在函数或多命令字符串中执行。

在 PostgreSQL 中，无法从存储过程中删除数据库吗？我正在使用 plpgsql。

- Rudecles

---

你可以从另一个数据库中完成它。 - Houari

您可以尝试使用 dblink 连接到此集群上的任何数据库（除了您想要删除的那个），并从 dblink 执行 DROP DATABASE IF EXISTS db_1;。 - Ihor Romanchenko

我相信错误信息非常清晰。 - Milen A. Radev

Answer 1

错误信息就像手册中所描述的那样明确：

DROP DATABASE 不能在事务块内执行。

plgpsql函数会自动被一个事务块包围。长话短说：你不能直接这样做。是否有什么特殊原因，使你不能调用DDL命令呢？

DROP database $mydb;

您可以借助额外的模块dblink绕过这些限制，正如@Igor所建议的一样。您需要在每个数据库中安装它一次，其中一个用于调用 dblink 函数，而不是执行命令的（另一个）数据库。
允许您编写使用dblink_exec()函数的代码，例如：

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() 可以防止 SQL 注入攻击。

调用方式：

SELECT f_drop_db('mydb');

成功时，您将看到以下内容：

DROP DATABASE

连接字符串甚至可以指向您的会话运行的同一数据库。该命令在事务块之外运行，这有两个后果：

• 无法回滚。
• 它允许您从函数内部“通过代理”调用DROP DATABASE。

您可以创建一个FOREIGN DATA WRAPPER和一个FOREIGN SERVER来存储连接并简化调用：

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');

使用默认维护数据库 postgres，这是一个显而易见的选择。但可以使用任何数据库。

利用此功能的简化函数:

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

- Erwin Brandstetter

Answer 2

您无法从存储过程中执行此操作，因为drop database无法在事务内执行，而存储过程本身被视为一个事务。(请参见reference)

dropdb怎么样？

- Houari

---

我正在从另一个数据库进行操作。存储过程在db_2中，并且我正在那里运行它。我将尝试从PostgreSQL中执行。 - Rudecles

这显然不是主要问题。你读过错误信息了吗？ - Erwin Brandstetter