如果表空間不存在如何創建

當我通過執行腳本在 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 語句作為字元串返回，但是如何執行呢？

這裡的重點是 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;

要點：

• 不要使用數據類型character。你要text.
  • 使用數據類型“文本”儲存字元串有什麼缺點嗎？
• 在檢查 時''，還要檢查NULL。
• 用於format()簡短、乾淨的動態 SQL 程式碼並正確轉義使用者輸入，以避免偷偷摸摸的語法錯誤和 SQL 注入。

帶有程式碼範例和更多解釋的相關答案：

• 即使函式中止，UDF 中的持久插入也是如此
• Postgres 是否支持嵌套或自治事務？

引用自：https://dba.stackexchange.com/questions/150148