主页 / dba / 问题 / 150148
Accepted
Marco
Asked: 2016-09-21 04:44:20 +0800 CST

如果表空间不存在如何创建

  • 772

当我通过执行脚本在 Postgres 中创建新模式时，psql我想将其放入表空间中。如果这个表空间不存在，那么我想先创建它。由于默认 SQL 没有此选项，因此我创建了一个函数：

CREATE OR REPLACE FUNCTION make_tablespace(tablespace CHARACTER,
                                           directory CHARACTER,
                                           owner CHARACTER)
  RETURNS void
AS
$$
BEGIN
  IF tablespace = '' THEN
    RAISE EXCEPTION 'No tablespace.';
  END IF;
  PERFORM SPCNAME FROM PG_TABLESPACE WHERE SPCNAME=tablespace;
  IF NOT FOUND THEN
    IF directory = '' THEN
      RAISE EXCEPTION 'No directory.';
    END IF;
    IF owner = '' THEN
      RAISE EXCEPTION 'No owner.';
    END IF;
    EXECUTE 'CREATE TABLESPACE '||tablespace||' OWNER '||owner||' LOCATION '''||directory||''';';
    RAISE NOTICE 'Tablespace % created.', tablespace;
  ELSE
    RAISE NOTICE 'Tablespace % already exists.', tablespace;
  END IF;
END $$ LANGUAGE plpgsql;

不幸的是，当我执行它时（select make_tablespace('marco', '/opt/marco', 'marco');），这给出了一个错误：

错误：无法从函数或多命令字符串执行 CREATE TABLESPACE

我搜索了互联网，似乎有一个解决方法（几年前）使用这个dblink包。我不想安装这个。今天还有其他方法吗？我可以将 SQL 语句作为字符串返回，但是如何执行呢？

postgresql postgresql-9.4

1 个回答

  1. Best Answer

    这里的重点是 Postgres 函数几乎但不完全像真正的存储过程。Postgres 函数（与存储过程不同）在外部事务的上下文中运行。因此，您不能执行不能在事务块中运行的命令，例如VACUUM, CREATE DATABASE, ... 或CREATE TABLESPACE. 手册对此很清楚：

    CREATE TABLESPACE不能在事务块内执行。

    您必须将这些命令作为单个 SQL 命令运行。Postgres 当前（最高版本 9.6）也没有自治事务，这可能是一种解决方法。

    因此，将命令包含在函数或事务中的唯一解决方法是使用 伪造自治事务dblink，就像您已经发现的那样。

    您的代码中还有其他一些小问题。我建议：

    CREATE OR REPLACE FUNCTION make_tablespace(tablespace text,
                                           directory text,
                                           owner text)
  RETURNS void AS
$func$
BEGIN
   IF tablespace <> '' THEN  -- catches '' *and* NULL
      -- do nothing
   ELSE
      RAISE EXCEPTION 'No tablespace.';
   END IF;

   IF EXISTS (SELECT 1 FROM pg_tablespace WHERE spcname = tablespace) THEN
      RAISE NOTICE 'Tablespace % already exists.', tablespace;
      EXIT;
   END IF;

   IF directory <> '' THEN
   ELSE
      RAISE EXCEPTION 'No directory.';
   END IF;

   IF owner <> '' THEN
   ELSE
      RAISE EXCEPTION 'No owner.';
   END IF;

   PERFORM dblink_connect('myserver');  -- name of foreign server
   PERFORM dblink_exec(format('CREATE TABLESPACE %I OWNER %I LOCATION %L', tablespace, owner, directory));
   RAISE NOTICE 'Tablespace % created.', tablespace;
   PERFORM dblink_disconnect();
END
$func$  LANGUAGE plpgsql;

    要点：

    带有代码示例和更多解释的相关答案：

    • 2

相关问题