使用帶有參數的 psql 變數的表達式創建序列

我已經用 Postgres 玩了一周左右，我正在查看是否可以使用表達式設置序列的最小/最大值。具體目標是為伺服器之間的不相交範圍自動創建腳本，以避免多主機設置中的密鑰衝突。

例如，使用 psql：

CREATE SEQUENCE key_seq MINVALUE (:servernum * :stride)
                       MAXVALUE ((:servernum + 1) * :stride - 1);

這給了我一個語法錯誤。變數是逐字插入的，沒有計算發生。

另一種方法是根據表達式的輸出設置變數：

\set minvalue (:servernum * :stride)

但該\set語句不評估表達式。

您需要\set（不是\pset！）設置psql變數。與 Unix shell 中的賦值不同，psql 賦值不能進行算術運算。您可以使用 psql 命令\!來執行 shell 命令，但我會建議兩種不同的方法：

選項 1：讓 Postgres 計算並設置新變數\gset

手冊關於\gset：

將目前查詢輸入緩衝區發送到伺服器並將查詢的輸出儲存到 psql 變數中

然後，您可以將計算出的數字內插為文本，一切正常。

psql程式碼：

test=# \set servernum 5
test=# \set stride 300
test=# SELECT :servernum * :stride AS minvalue, (:servernum + 1) * :stride - 1 AS maxvalue \gset
test=# CREATE SEQUENCE key_seq MINVALUE :minvalue MAXVALUE :maxvalue;
CREATE SEQUENCE

一個很好的部落格解釋\gset：

• http://marxsoftware.blogspot.co.at/2015/08/postgresql-psql-variable-from-query.html

我計算並設置:minvalue並:maxvalue在一個單一 SELECT的以優化性能。不過，這種方法的缺點是您需要額外往返 Postgres 伺服器。

選項 2：具有動態 SQL 以供重複使用的函式

您可以使用DO聲明，但是（再次使用手冊）：

變數插值不會在引用的 SQL 文字和標識符中執行。

這會使字元串連接複雜化。我建議創建一個函式並format()用於乾淨的程式碼。如果它僅用於目前會話，則可以通過模式限定使其成為臨時函式，pg_temp.因此該函式僅對目前會話可見並在會話結束時刪除。臨時函式是一個未記錄的特性——儘管 Tom Lane 提出了建議。細節：

• 如何在 Postgres 中創建臨時函式

所以：

test=# CREATE FUNCTION pg_temp.f_my_seq(_seq text, _servernum int, _stride int)
 RETURNS void AS
$func$
BEGIN
  EXECUTE format('CREATE SEQUENCE %I MINVALUE %s MAXVALUE %s'
                , _seq, _servernum * _stride, (_servernum + 1) * _stride - 1);
END
$func$ LANGUAGE plpgsql;

test=# \set servernum 5
test=# \set stride 300

test=# SELECT pg_temp.f_my_seq('key_seq', :servernum, :stride);

我們需要動態 SQL 和EXECUTE. 關於變數替換的手冊：

變數替換目前僅適用於SELECT、INSERT、UPDATE和DELETE命令，因為主 SQL 引擎僅允許在這些命令中使用查詢參數。要在其他語句類型（通常稱為實用程序語句）中使用非常量名稱或值，您必須將實用程序語句構造為字元串和EXECUTE它。

關於 SO 的相關答案：

• 使用 plpgsql 變數設置 n_distinct 時出錯

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