如何在 PostgreSQL 查詢中宣告變數

一般 PostgreSQL SQL 查詢無法宣告通用變數。若要在查詢中使用某個值，可以在查詢範圍內的 WITH 子句中定義它、在 PL/pgSQL 區塊內宣告程序變數，或透過自訂設定傳入值。這些方法的作用範圍與存續時間各不相同。應用程式程式碼通常應使用資料庫驅動程式提供的綁定參數。

在 PostgreSQL 查詢中宣告變數

以下範例使用一個有三筆資料的 vehicle 資料表。以下 SQL 是在 PostgreSQL 資料庫中建立範例資料的語法。vehicle_name、vehicle_type 和 vehicle_model 是 varchar 欄位，vehicle_price 則是整數欄位。

使用 CREATE TABLE vehicle (vehicle_name varchar(30), vehicle_type varchar(30), vehicle_model varchar(30), vehicle_price integer, id serial UNIQUE NOT NULL PRIMARY KEY); 建立資料表，再用 INSERT INTO vehicle (vehicle_name, vehicle_type, vehicle_model, vehicle_price) VALUES ('Jaguar Range Rover Evoque', '4WD', '2019 Model', 7000000), ('Porsche Cayenne', '4WD', '2019 Model', 7000000), ('BMW 1 Series', '4WD', '2019 Model', 7000000); 加入範例資料。

請在要測試範例的資料庫中執行一次建表與新增資料的語法。以下各查詢都會篩選出價格為 7000000 的車輛。

在應用程式程式碼中，請透過 PostgreSQL 驅動程式的參數 API 綁定值，不要把值直接插入 SQL 字串。以下 Python 範例會顯示 SQL 範本及獨立傳入的參數值。不同 PostgreSQL 驅動程式使用的佔位符格式可能不同，請依照所用驅動程式的文件選擇格式。

query = "SELECT vehicle_name FROM vehicle WHERE vehicle_price = %s;"
params = (7000000,)
print(query)
print(params)

SELECT vehicle_name FROM vehicle WHERE vehicle_price = %s;
(7000000,)

使用 WITH 子句在 PostgreSQL 查詢中宣告變數

WITH 子句會為單一陳述式定義通用資料表運算式（CTE）。此處的 prices 是查詢範圍內、包含待比較值的關聯資料；它不是通用變數，也不是暫存資料表。

使用 WITH prices AS (SELECT 7000000 AS car_price) SELECT vehicle_name FROM vehicle CROSS JOIN prices WHERE vehicle.vehicle_price = prices.car_price;。

使用上述範例資料列時，查詢會傳回三個車名：Jaguar Range Rover Evoque、Porsche Cayenne 和 BMW 1 Series。CTE 只在緊接其後的單一陳述式中有效；若要用於另一個陳述式，必須重新定義。

使用 PostgreSQL 程序語言在查詢中宣告變數

PL/pgSQL 的 DO 區塊可以宣告變數，並在區塊內執行程序式工作。變數 price 的作用範圍僅限於此區塊。此範例會將符合條件的資料寫入暫存資料表；該資料表預設會在目前資料庫工作階段存續，並於工作階段結束時刪除。

執行區塊 DO $$ DECLARE price integer := 7000000; BEGIN CREATE TEMP TABLE expvehicles AS SELECT vehicle_name FROM vehicle WHERE vehicle_price = price; END $$;，接著以另一個獨立陳述式執行 SELECT vehicle_name FROM expvehicles; 讀取結果。

CREATE TEMP TABLE 是區塊內執行的 SQL 工作；後續的 SELECT 則是獨立陳述式。如果改用 CREATE TABLE，除非明確指定建立暫存資料表，否則 PostgreSQL 會建立永久資料表。重新執行範例時，請選用不同的資料表名稱，或先移除既有資料表；若目前工作階段中已經有同名暫存資料表，就可能無法再次建立。

使用動態設定在 PostgreSQL 查詢中宣告變數

自訂設定可以在同一資料庫工作階段的多個陳述式之間傳遞值。使用 SET 指定設定，再用 current_setting 讀取。由於 current_setting 傳回文字，與 vehicle_price 比較前必須先將值轉型為整數。

使用 SET my.vars.price = '7000000'; 指定值，再以 SELECT vehicle_name FROM vehicle WHERE vehicle_price = current_setting('my.vars.price')::integer; 查詢。

此設定會在整個工作階段中保持可用。若要將值的作用範圍限制在目前交易，請先開始交易，再執行 SET LOCAL my.vars.price = '7000000'；交易提交或回復時，本地設定值就會結束。對應用程式查詢而言，使用驅動程式綁定參數通常更容易管理作用範圍，也比工作階段設定安全，尤其是在連線池會重複使用連線時。