使用目前行和目前表名作為變數的觸發函式（最後部分）

就像我在第一個問題中詳述的那樣，我有一個Postgres 9.1數據庫，其中包含多個具有完全相同列名的表，它們僅在列值上有所不同：

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_timetypespan()
 RETURNS trigger AS
$BODY$
DECLARE
v_timetype character varying;
v_timestmp_timetype timestamp without time zone;
v_timetypespan_resume interval;
v_stmtserial real;
v_sumtimetypespan_fnname interval;

BEGIN

IF NEW.timetype = 'lap' THEN
 SELECT timetype, timestmp, timetypespan FROM tbl_log_a WHERE fnname = NEW.fnname AND (timetype = 'start' OR timetype = 'resume') ORDER BY stmtserial DESC LIMIT 1 INTO v_timetype, v_timestmp_timetype, v_timetypespan_resume;
   IF v_timetype = 'start' THEN
     NEW.timetypespan := age(NEW.timestmp, v_timestmp_timetype);
   ELSIF v_timetype = 'resume' THEN
     SELECT timestmp FROM tbl_log_a WHERE fnname = NEW.fnname AND timetype = 'start' ORDER BY stmtserial DESC LIMIT 1 INTO v_timestmp_timetype;
     NEW.timetypespan := age(NEW.timestmp, v_timestmp_timetype) - v_timetypespan_resume;
   ELSE
     RAISE EXCEPTION USING MESSAGE = 'There is not any previous row...';
   END IF;

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

ELSIF NEW.timetype = 'total' THEN
 SELECT stmtserial FROM tbl_log_a WHERE fnname = NEW.fnname AND timetype = 'total' ORDER BY stmtserial DESC LIMIT 1 INTO v_stmtserial;
 SELECT SUM(timetypespan) FROM (SELECT DISTINCT ON (floor(timeidx)::int) floor(timeidx)::int timeidx, timetypespan
 FROM tbl_log_a WHERE fnname = NEW.fnname AND timetype = 'lap' AND stmtserial > coalesce(v_stmtserial, 0) ORDER BY 1, 2 DESC) a INTO v_sumtimetypespan_fnname;
   IF v_sumtimetypespan_fnname NOTNULL THEN
     NEW.timetypespan := v_sumtimetypespan_fnname;
   ELSE
     RAISE EXCEPTION USING MESSAGE = 'There is not any previous row...';
   END IF;

END IF;
return NEW;

END
$BODY$
 LANGUAGE plpgsql VOLATILE;

觸發器定義：

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

所以我必須創建 26 個觸發函式，每個tbl_log_%letter%. 我正在嘗試用一個通用的觸發器函式替換所有這些。

在我之前的問題下，我們用一個簡化的函式制定了一個動態 SQL 的解決方案。可以將相同的技術擴展到這個更複雜的場景嗎？

EXECUTE format($$...

是的，這應該有效（未經測試）：

CREATE OR REPLACE FUNCTION trfn_tbl_log_timetypespan()  -- generic name
 RETURNS trigger AS
$func$
DECLARE
  _timetype varchar;
  _timetypespan_resume interval;
  _ct int;
BEGIN

CASE NEW.timetype
WHEN 'lap' THEN
  EXECUTE format($$
     SELECT timetype, timetypespan, age($1, timestmp)
     FROM   %s
     WHERE  fnname = $2
     AND    timetype IN ('start', 'resume')
     ORDER  BY stmtserial DESC
     LIMIT  1$$
   , TG_RELID::regclass)
  USING NEW.timestmp, NEW.fnname
  INTO  _timetype, _timetypespan_resume, NEW.timetypespan;

  CASE _timetype
  WHEN 'start' THEN  -- do nothing

  WHEN 'resume' THEN
     EXECUTE format($$
        SELECT age($1, timestmp) - _timetypespan_resume
        FROM   %s
        WHERE  fnname = $2
        AND    timetype = 'start'
        ORDER  BY stmtserial DESC
        LIMIT  1$$
      , TG_RELID::regclass)
     USING NEW.timestmp, NEW.fnname
     INTO  NEW.timetypespan;

  ELSE
     RAISE EXCEPTION 'There is no previous row.';
  END CASE;

WHEN 'resume' THEN
  EXECUTE format($$
     SELECT age($1, timestmp)
     FROM   %s
     WHERE  fnname = $2
     AND    timetype IN ('start', 'lap')
     ORDER  BY stmtserial DESC LIMIT 1$$
   , TG_RELID::regclass)
  USING NEW.timestmp, NEW.fnname
  INTO  NEW.timetypespan;

  GET DIAGNOSTICS _ct = ROW_COUNT;

  IF _ct > 0 THEN  -- do nothing
  ELSE
     RAISE EXCEPTION 'There is no previous row.';
  END IF;

WHEN 'total' THEN
  EXECUTE format($$
     SELECT COALESCE(SUM(timetypespan), $1)
     FROM (
        SELECT floor(timeidx)::int, max(timetypespan) AS timetypespan
        FROM   %1$s
        WHERE  fnname = $2
        AND    timetype = 'lap'
        AND    stmtserial > coalesce(
                (SELECT stmtserial
                 FROM   %1$s
                 WHERE  fnname = $2
                 AND    timetype = 'total'
                 ORDER  BY stmtserial DESC
                 LIMIT  1), 0)
        GROUP  BY 1
        ) sub$$
   , TG_RELID::regclass)
  USING NEW.timetypespan, NEW.fnname
  INTO  NEW.timetypespan;

  GET DIAGNOSTICS _ct = ROW_COUNT;

  IF _ct > 0 THEN  -- do nothing
  ELSE
     RAISE EXCEPTION 'There is no previous row.';
  END IF;
END CASE;

RETURN NEW;

END
$func$  LANGUAGE plpgsql;

這是我之前對您上一個問題的回答的後續行動。在那裡找到解釋：

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

在此過程中，我簡化了一些事情。比如：SELECT你的第一個ELSIF分支中的第二個只是SELECT上面一個級別的重複。我免費合併了它。

我還刪除了一些不必要的中間步驟，並NEW在適用的情況下直接分配給欄位。這就是為什麼我可以刪除你的大部分變數。

旁白：如果timeidx只有正數，您可以使用更便宜的trunc(timeidx)而不是floor(timeidx).

為了更容易理解動態部分…

如果您只為實現相同的tbl_log_a- 即tbl_log_a（在應用format()and之後EXECUTE）有效執行的程式碼如下所示：

...

CASE NEW.timetype
WHEN 'lap' THEN
  SELECT timetype, timetypespan, age(NEW.timestmp, timestmp)
  FROM   tbl_log_a
  WHERE  fnname = NEW.fnname
  AND    timetype IN ('start', 'resume')
  ORDER  BY stmtserial DESC
  LIMIT  1
  INTO  _timetype, _timetypespan_resume, NEW.timetypespan;

  CASE _timetype
  WHEN 'start' THEN  -- do nothing

  WHEN 'resume' THEN
     SELECT age(NEW.timestmp, timestmp) - _timetypespan_resume
     FROM   tbl_log_a
     WHERE  fnname = NEW.fnname
     AND    timetype = 'start'
     ORDER  BY stmtserial DESC
     LIMIT  1
     INTO  NEW.timetypespan;

...

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