postgresql 使用存储函数创建数据库
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4386118/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Create database using a stored function
提问by MySQL DBA
I am new to PostgreSQL and want to create a database using a stored function.
For ex:
我是 PostgreSQL 的新手，想使用存储的函数创建一个数据库。
例如：
CREATE OR REPLACE FUNCTION mt_test(dbname character varying)
RETURNS integer AS
$BODY$
Create Database ;
Select 1;
$BODY$
LANGUAGE sql;
When I am trying to execute this function I get a syntax error.
当我尝试执行此函数时，出现语法错误。
Does Postgres support the CREATE DATABASEstatement in stored functions?
Postgres 是否支持CREATE DATABASE存储函数中的语句？
回答by Erwin Brandstetter
This question is old, but for the sake of completeness ...
这个问题很旧，但为了完整起见......
As has been pointed out in other answers, that's not simply possible because (per documentation):
正如其他答案中指出的那样，这不仅仅是可能的，因为（根据文档）：
CREATE DATABASEcannot be executed inside a transaction block.
CREATE DATABASE不能在事务块内执行。
It has also been reported that the restriction can be bypassed with dblink.
How to use (install) dblink in PostgreSQL?
据报道，该限制可以通过dblink.
如何在 PostgreSQL 中使用（安装）dblink？
What was missing so far is a proper function actually doing it:
到目前为止缺少的是实际执行此操作的正确功能：
CREATE OR REPLACE FUNCTION f_create_db(dbname text)
RETURNS integer AS
$func$
BEGIN
IF EXISTS (SELECT 1 FROM pg_database WHERE datname = dbname) THEN
RAISE NOTICE 'Database already exists';
ELSE
PERFORM dblink_exec('dbname=' || current_database() -- current db
, 'CREATE DATABASE ' || quote_ident(dbname));
END IF;
END
$func$ LANGUAGE plpgsql;
Checks if the db already exists in the local cluster. If not, proceed to create it - with a sanitized identifier. We would not want to invite SQL injection.
检查 db 是否已存在于本地集群中。如果没有，请继续创建它 - 使用经过消毒的标识符。我们不想邀请 SQL 注入。
回答by Esteban Atenor
I found a tricky solution to this problem, but possible. After looking and reading almost in everywhere I tried something and it worked.
我找到了一个棘手的解决方案，但可能。在几乎无处不在的地方查看和阅读之后，我尝试了一些方法并且它奏效了。
if the error is "CREATE DATABASE cannot be executed from a function or multi-command string" we can force a single command string using dblink. And make it to connect to itself.
如果错误是“无法从函数或多命令字符串执行 CREATE DATABASE”，我们可以使用 dblink 强制执行单个命令字符串。并使其连接到自身。
Check for dblink installation instructions at dblink
在dblink检查 dblink 安装说明
PERFORM replication.dblink_connect('myconn','host=127.0.0.1 port=5432 dbname=mydb user=username password=secret');
PERFORM replication.dblink_exec('myconn', 'CREATE DATABASE "DBFROMUSER'||id||'" TEMPLATE "TEMPL'||type||'";',false);
PERFORM replication.dblink_disconnect('myconn');
In my case using different kinds of template.
就我而言，使用不同类型的模板。
Greetings
你好
回答by Milen A. Radev
You can't create a database inside of a function because it's not possible to create a database inside a transaction.
您不能在函数内部创建数据库，因为不可能在事务内部创建数据库。
But most probably you don't mean to create databases but schemas, which more closely resemble the MySQL's databases.
但很可能您不是要创建数据库，而是要创建schemas，它更类似于 MySQL 的数据库。
回答by Milen A. Radev
postgres=> create or replace function mt_test(dbname text)
returns void language plpgsql as $$
postgres$> begin
postgres$> execute 'create database '||;
postgres$> end;$$;
CREATE FUNCTION
postgres=> select work.mt_test('dummy_db');
ERROR: CREATE DATABASE cannot be executed from a function or multi-command string
CONTEXT: SQL statement "create database dummy_db"
PL/pgSQL function "mt_test" line 2 at EXECUTE statement
postgres=>
note the error message: CREATE DATABASE cannot be executed from a function or multi-command string
请注意错误消息： CREATE DATABASE cannot be executed from a function or multi-command string
so the answer to the question:
所以问题的答案是：
Does postgresql support creating statement in stored function
postgresql 是否支持在存储函数中创建语句
is "no" (at least on 8.4 - you don't specify your version)
是“否”（至少在 8.4 上 - 您没有指定您的版本）
