postgre创建存储过程_在PostgreSQL中从存储过程删除或创建数据库

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.

解决方案

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