Postgresql

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

  • July 29, 2020

當我通過執行腳本在 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;

要點:

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

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