Postgresql
如果表空間不存在如何創建
當我通過執行腳本在 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 注入。帶有程式碼範例和更多解釋的相關答案: