使用目前行和目前表名作為變數的觸發函式

就像我在第一個問題中詳述的那樣，我在Postgres 9.1 DB中有多個佈局相同的表。

它們僅在列值上有所不同：

tbl_log_a
tbl_log_b
tbl_log_c
...

26 個表（從 a 到 z）。每個表都有一個觸發器，該觸發器呼叫一個名為trfn_tbl_log_%letter%(from ato z) 的觸發器函式，該函式執行完全相同的操作：

CREATE OR REPLACE FUNCTION trfn_tbl_log_a
 RETURNS trigger AS
$BODY$
DECLARE
v_timeidx real;

BEGIN

IF NEW.timetype = 'start' THEN
 SELECT timeidx FROM tbl_log_a
 WHERE fnname = NEW.fnname AND timetype = 'start'
 ORDER BY stmtserial DESC LIMIT 1 INTO v_timeidx;
   IF FOUND THEN
     NEW.timeidx := floor(v_timeidx) + 1;
   ELSE
     NEW.timeidx := 1;
   END IF;

ELSIF NEW.timetype = 'lap' THEN 
 SELECT timeidx FROM tbl_log_a
 WHERE fnname = NEW.fnname AND (timetype = 'start' OR timetype = 'lap')
 ORDER BY stmtserial DESC LIMIT 1 INTO v_timeidx;
   IF FOUND THEN
     NEW.timeidx := v_timeidx + 0.001;
   ELSE
     RAISE EXCEPTION USING MESSAGE = 'There is not any previous row WHERE fnname = NEW.fnname AND (timetype = start OR timetype = lap)';
   END IF;

ELSIF NEW.timetype = 'resume' THEN
 SELECT timeidx FROM tbl_log_a
 WHERE fnname = NEW.fnname AND (timetype = 'start' OR timetype = 'resume')
 ORDER BY stmtserial DESC LIMIT 1 INTO v_timeidx;
   IF FOUND THEN
     NEW.timeidx := v_timeidx + 0.001;
   ELSE
     RAISE EXCEPTION USING MESSAGE = 'There is not any previous row WHERE fnname = NEW.fnname AND timetype = start';
   END IF;

END IF;
return NEW;

END
$BODY$
 LANGUAGE plpgsql;

觸發器定義：

CREATE TRIGGER trfn_tbl_log_a
 BEFORE INSERT ON tbl_log_a
 FOR EACH ROW EXECUTE PROCEDURE trfn_tbl_log_a();

所以我必須創建 26 個觸發器函式，每個觸發器函式tbl_log_%letter% 都完全相同，除了使用的表名（tbl_log_a在範例中）。

有沒有辦法編寫一個通用觸發器函式，可能使用動態 SQL 並參數化表名？

我的觸發器函式使用了幾個表列：

timeidx
fnname
timetype
stmtserial

…還有更多我沒有為尺寸添加的內容，但範例中的所有內容都列出了所有類型。

假設對於同一個觸發器呼叫，您從觸發觸發器的表中的同一行中獲取所有值，您的觸發器函式可能如下所示：

CREATE OR REPLACE FUNCTION trfn_tbl_log_any()
 RETURNS trigger AS
$func$
DECLARE
  _ct int;
BEGIN

IF NEW.timetype = 'start' THEN

  EXECUTE format($$
     SELECT floor(t.timeidx) + 1
     FROM   %s t
     WHERE  t.fnname = $1
     AND    t.timetype = 'start'
     ORDER  BY t.stmtserial DESC
     LIMIT  1$$
   , TG_RELID::regclass  -- concatenate *identifer* ..
     )
  USING NEW.fnname        -- .. but pass *value* in USING clause
  INTO  NEW.timeidx;

  GET DIAGNOSTICS _ct = ROW_COUNT;

  IF _ct > 0 THEN  -- do nothing
  ELSE
     NEW.timeidx := 1;
  END IF;
END IF;

RETURN NEW;
END
$func$  LANGUAGE plpgsql;

所有這些都應該在 Postgres 9.1 中工作。但無論如何都要考慮升級到目前版本（目前為 9.4）。

• NEW裡面是不可見的EXECUTE。使用該USING子句傳遞新行 ( NEW.fnname) 中的值。

• 使用TG_RELID（或TG_TABLE_SCHEMA和TG_TABLE_NAME）連接表名，就像我們在您之前的問題中製定的那樣：

  • 從 %current% 表中選擇的 PostgreSQL 觸發器函式

• 使用format()和美元引號來安全地簡化字元串連接的語法。

• NEW您可以直接從動態查詢中分配行的各個列。

• 用於GET DIAGNOSTICS _ct = ROW_COUNT;檢查是否找到行。根據文件：

特別注意，EXECUTE改變 的輸出GET DIAGNOSTICS，但不改變FOUND。

順便說一句：動態查詢的邏輯只對BEFORE觸發器是正確的。AFTER觸發器也會看到新插入的行。

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