在PostgreSQL中从存储过程中删除或创建数据库 [英] Drop or create database from stored procedure in PostgreSQL

问题描述

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

I have a function that looks like this:

BEGIN
  DROP DATABASE IF EXISTS db_1;
END;

我遇到以下错误:

错误:无法从函数或多命令字符串中执行DROP DATABASE.

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

是否无法从PostgreSQL的存储过程中删除数据库?我正在使用plpgsql.

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

推荐答案

错误消息与

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

DROP DATABASE cannot be executed inside a transaction block.

plgpsql函数自动被事务块包围.它的长短:您不能直接这样做.您有不能仅调用DDL命令的特殊原因吗?

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;

您可以使用附加模块 dblink 作为 dblink_exec() 编写函数像这样:

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()防止可能的SQL注入.

quote_ident() prevents possible SQL injection.

致电:

SELECT f_drop_db('mydb');

成功后，您会看到:

DROP DATABASE

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:

• 它无法回滚.
• 它允许您从函数内通过代理方式"调用DROP DATABASE.

您可以创建一个FOREIGN DATA WRAPPER和FOREIGN SERVER来存储连接并简化呼叫:

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

使用默认维护数据库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$;

这篇关于在PostgreSQL中从存储过程中删除或创建数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！