使用存储的函数创建数据库 [英] Create database using a stored function

问题描述

我是PostgreSQL的新用户，想要使用存储的函数创建数据库。

例如：

I am new to PostgreSQL and want to create a database using a stored function.
For ex:

CREATE OR REPLACE FUNCTION mt_test(dbname character varying)
  RETURNS integer AS
$BODY$

Create Database $1;

Select 1;

$BODY$
  LANGUAGE sql;

当我试图执行此函数时，我得到语法错误。

When I am trying to execute this function I get a syntax error.

Postgres支持存储函数中的 CREATE DATABASE 语句吗？

Does Postgres support the CREATE DATABASE statement in stored functions?

推荐答案

这个问题很老，但是为了完整起见...

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 DATABASE

也有人报告说，可以通过 dblink 。

如何在postgresql中使用（安装）dblink？

It has also been reported that the restriction can be bypassed with dblink.
How to use (install) dblink in postgresql?

到目前为止，缺少的是一个合适的函数：

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;

检查数据库是否已存在于本地集群中。如果没有，请继续创建它 - 使用已清理的标识符。我们不想邀请SQL注入。

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.

这篇关于使用存储的函数创建数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！