以列名作為參數修改行的觸發函式

我正在嘗試在 Postgres 9.4 中編寫一個觸發函式。像這樣的東西（還沒有工作）：

 CREATE FUNCTION set_point_from_coords(source _regclass, target _regclass) 
   RETURNS trigger AS $func$
 BEGIN
   NEW.target := ST_SetSRID(ST_Point(NEW.source[1], NEW.source[2]), 4326);
   RETURN NEW;
 END;
 $func$ LANGUAGE plpgsql

在這種情況下，target是一列類型geometry並且source是一個小數數組。

當用coords數組插入一行時，我想將其轉換為point. 如果我只是對列名進行硬編碼，上述方法將起作用，但我想使用相同的函式對不同的表和不同的列對執行此操作。而且我無法直接控制它INSERT本身。

這是我的一些實驗：http ://sqlfiddle.com/#!15/dddcd/1

找到了這篇相關的部落格文章，我很難解析。

如果這樣更容易編碼，我可以在插入/更新之後而不是之前執行這個執行。

問題

你必須選擇所有可能的並發症都聚集在一起的地方。

• SQL（或 PL/pgSQL）不允許參數化標識符。為此，您需要使用動態 SQL EXECUTE。
• 但是觸發器函式中的特殊 plpgsql 變數NEW在使用EXECUTE.
• 通過將列名作為參數傳遞給CREATE TRIGGER.
• 使目標列動態化是不夠的，您還想從該行的另一個動態列中獲取源值。

除非您了解所有涉及的問題，否則我寧願嘗試更簡單的方法。為每個觸發器編寫一個單獨的觸發器函式，並在沒有動態 SQL 的情況下分配給目標列。

解決方案

也就是說，它可以通過一行程式碼和多行解釋來完成。對於問題中的原始範例，假設此表定義：

CREATE TABLE tbl (
 tbl_id serial PRIMARY KEY,
 geom geometry,
 coords double precision[]
);

您需要安裝附加模組hstore（每個數據庫一次）才能正常工作。或者，您也可以出於相同目的使用json_populate_record()/的未記錄功能：jsonb_populate_record()

• 如何使用動態 SQL 設置複合變數欄位的值

CREATE OR REPLACE FUNCTION trg_demo()
 RETURNS trigger AS 
$func$
BEGIN
  EXECUTE format('SELECT ($1 #= hstore(%L, **ST_SetSRID(ST_Point($1.%2$I[1], $1.%2$I[2]), 4326)**::text)).*'
               , TG_ARGV[0], TG_ARGV[1])  -- target (geom), source (coords)
  USING  NEW
  INTO   NEW;
  RETURN NEW;
END
$func$ LANGUAGE plpgsql;

扳機：

CREATE TRIGGER demo
BEFORE INSERT OR UPDATE ON tbl
FOR EACH ROW EXECUTE PROCEDURE trg_demo('geom', 'coords');

如果你不明白這裡發生了什麼，請考慮我上面的建議。

解釋

我格式化了geometry 粗體的動態計算以幫助您掌握這一點。與下面的簡單案例進行比較。

db<>fiddle here - 使用point而不是geometry, 因為沒有安裝 PostGIS。

老sqlfiddle。

這是一個更簡單的版本，只需分配裸text值（這將要求目標列geom是 type text）。再次以粗體顯示的簡化部分：

  EXECUTE format('SELECT ($1 #= hstore(%L, **$1.%I**::text)).*'
               , TG_ARGV[0], TG_ARGV[1])  -- target (geom), source (coords)
  USING  NEW
  INTO   NEW;

核心功能是hstore 運算符**#=**（根據文件）：

record用匹配的值替換中的欄位hstore

目標和源都是新行的列，這使事情變得複雜。如果源值是一個常數，我們可以簡單地：

NEW := NEW #= hstore(TG_ARGV[0], 'POINT(123.0 456.0)');

有關的：

• 在 Postgres 觸發器中按鍵分配給 NEW

但是我們需要動態 SQL 來解析列名並獲取源值。

• 用於format()安全地連接查詢字元串。
• TG_ARGV[0]並將TG_ARGV[1]前兩個元素訪問到由CREATE TRIGGER.
• %I連接format()作為標識符傳遞給的參數（對 SQL 注入安全）。
• $1引用EXECUTE在USING子句中傳遞給的值。
• 我們需要::text在計算幾何圖形後進行轉換，因為hstore需要text.
• 我們需要將NEW分配的行分解回來，因為 plpgsql 將行分配為逐列的目標。

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