修改函式以在多個觸發器中使用它

我有幾乎相同列的表。第一個表具有目前值，另一個表具有舊值，並且插入了一個用於儲存日期數據的附加列。第二個表被命名為帶有前綴**_bit**的第一個表。例子：

CREATE TABLE x (
 id serial PRIMARY KEY,
 f1 integer NOT NULL,
 f2 character varying(10) NOT NULL,
 cuando timestamp without time zone NOT NULL DEFAULT now()
);

CREATE TABLE _bitx (
 id integer,
 f1 integer,
 f2 character varying(10),
 cuando timestamp without time zone,
 cuandobit timestamp without time zone DEFAULT now()
);

我有一個觸發器函式，可以將OLD一個表的值複製到另一個：

CREATE FUNCTION bitacoratabla() RETURNS trigger AS $$
  BEGIN
     INSERT INTO _bitx VALUES((OLD).*);
     RETURN NEW;
  END;
$$ LANGUAGE plpgsql

觸發：

CREATE TRIGGER trigbitx
 BEFORE UPDATE
 ON public.x
 FOR EACH ROW
 EXECUTE PROCEDURE public.bitacoratabla();

所有這一切都很好，但我必須對遵循相同規則的多個表執行此操作。

我修改了函式以創建將插入數據的表的名稱，但我遇到了問題和**（OLD）。***

CREATE FUNCTION bitacoratabla() RETURNS trigger AS $$
  DECLARE
     tabla text := '_bit' || quote_ident(TG_TABLE_NAME);

  BEGIN
     EXECUTE 'INSERT INTO ' || tabla || ' VALUES(' || (OLD).* || ')';
     RETURN NEW;
  END;
$$ LANGUAGE plpgsql

我是 PL/pgSQL 的新手，並認為我不必做更多。有任何想法嗎？

更新

正如您所建議的那樣，最好使用關鍵字format()並傳遞值。謝謝。這就是我所做的：USING

CREATE OR REPLACE FUNCTION bitacoratabla() RETURNS TRIGGER 
AS $$
  BEGIN
     EXECUTE format('INSERT INTO %I SELECT $1.*', '_bit' || TG_TABLE_NAME) USING OLD;
     RETURN NEW;
  END 
$$ LANGUAGE PLPGSQL;

到目前為止，答案仍然存在一個或另一個缺點。

1. 使用USINGof 子句EXECUTE傳遞值。不要將值的文本表示形式連接到 SQL 字元串中。昂貴且容易出錯。
2. 在動態 SQL 中使用時正確轉義所有標識符，以防止帶有雙引號標識符和可能的 SQL 注入的偷偷摸摸的錯誤。即使你認為你不需要它。原則上做。

但這將是一個等待發生的錯誤：

'_bit' || quote_ident(TG_TABLE_NAME)

對於一個表名"MyTable"，這會產生_bit"MyTable"- 句法廢話。一定是：

quote_ident('_bit' || TG_TABLE_NAME)

或使用format('... %I ...', '_bit' || TG_TABLE_NAME)

唯一正確的解決方案是您自己添加到問題中的內容：

CREATE OR REPLACE FUNCTION bitacoratabla()
 RETURNS trigger AS
$func$
BEGIN
  EXECUTE format('INSERT INTO %I SELECT $1.*', '_bit' || TG_TABLE_NAME)
  USING OLD;

  RETURN NEW;
END
$func$  LANGUAGE plpgsql;

詳細解釋：

• 在觸發器函式中插入動態表名

但為什麼它可以與額外的列一起使用？

另一個表有舊值和一個額外的列來儲存日期數據

只要目標表中的附加列最後出現，這就會起作用，因為引用手冊INSERT：

如果根本沒有給出列名列表，則預設為按聲明順序排列的表的所有列；或前N列名稱，如果子句或查詢僅提供N列。VALUES

因此，您最好確保附加列在目標表的表定義中排在最後，並且所有其他列現在和將來都按匹配順序列出 - 或者您必須添加一個明確的目標列列表（這將防止泛型函式開始）。

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