如何在 PostgreSQL 8.2 中动态使用 TG_TABLE_NAME？

Question

我正在尝试在 PostgreSQL 8.2 中编写一个触发器函数，该函数将动态使用 TG_TABLE_NAME 生成并执行 SQL 语句。我可以找到 PostgreSQL 更高版本的各种示例，但由于某些要求，我被困在 8.2 上。这是我的函数，它可以工作，但几乎不是动态的：

CREATE OR REPLACE FUNCTION cdc_TABLENAME_function() RETURNS trigger AS $cdc_function$
        DECLARE 
        op  cdc_operation_enum;
    BEGIN
        op = TG_OP;

        IF (TG_WHEN = 'BEFORE') THEN
            IF (TG_OP = 'UPDATE') THEN
                op = 'UPDATE_BEFORE';
            END IF;

            INSERT INTO cdc_test VALUES (DEFAULT,DEFAULT,op,DEFAULT,DEFAULT,OLD.*); 
        ELSE
            IF (TG_OP = 'UPDATE') THEN
                op = 'UPDATE_AFTER';
            END IF;

            INSERT INTO cdc_test VALUES (DEFAULT,DEFAULT,op,DEFAULT,DEFAULT,NEW.*); 
        END IF;

        IF (TG_OP = 'DELETE') THEN
            RETURN OLD;
        ELSE
            RETURN NEW;
        END IF;
    END;

按照当前编写的方式，我必须为每个表编写一个单独的触发器函数。我想使用 TG_TABLE_NAME 动态构建我的 INSERT 语句，并在其前面加上“cdc_”前缀，因为所有表都遵循相同的命名约定。然后我可以让每个表的每个触发器只调用一个函数。

Answer 1

几年前我一直在寻找完全相同的东西。一种触发功能即可统治一切！我在 usenet 列表上询问，尝试了各种方法，但无济于事。关于此事的共识是这是不可能的。 PostgreSQL 8.3 或更早版本的一个缺点。

自 PostgreSQL 8.4< /strong> 你可以：

EXECUTE 'INSERT INTO ' || TG_RELID::regclass::text || ' SELECT ($1).*'
USING NEW;

使用 pg 8.2，你遇到一个问题：

• 无法动态访问 NEW / OLD 列。你需要知道
  编写触发器函数时的列名称。
• NEW / OLD 在EXECUTE 内不可见。
• EXECUTE .. USING 尚未诞生。

然而，有一个技巧。

系统中的每个表名都可以作为同名的复合类型。因此，您可以创建一个以 NEW / OLD 作为参数的函数并执行它。您可以在每个触发事件上动态创建和销毁该函数：

触发函数：

CREATE OR REPLACE FUNCTION trg_cdc()
  RETURNS trigger AS
$func$
DECLARE
   op      text := TG_OP || '_' || TG_WHEN;
   tbl     text := quote_ident(TG_TABLE_SCHEMA) || '.'
                || quote_ident(TG_TABLE_NAME);
   cdc_tbl text := quote_ident(TG_TABLE_SCHEMA) || '.'
                || quote_ident('cdc_' || TG_TABLE_NAME);
BEGIN

EXECUTE 'CREATE FUNCTION f_cdc(n ' || tbl || ', op text)
  RETURNS void AS $x$ BEGIN
  INSERT INTO ' || cdc_tbl || ' SELECT op, (n).*;
END $x$ LANGUAGE plpgsql';

CASE TG_OP
WHEN 'INSERT', 'UPDATE' THEN
   PERFORM f_cdc(NEW, op);
WHEN 'DELETE' THEN
   PERFORM f_cdc(OLD, op);
ELSE
   RAISE EXCEPTION 'Unknown TG_OP: "%". Should not occur!', TG_OP;
END CASE;

EXECUTE 'DROP FUNCTION f_cdc(' || tbl || ', text)';

IF TG_OP = 'DELETE' THEN
    RETURN OLD;
ELSE
    RETURN NEW;
END IF;

END
$func$  LANGUAGE plpgsql;

触发器：

CREATE TRIGGER cdc
BEFORE INSERT OR UPDATE OR DELETE ON my_tbl
FOR EACH ROW EXECUTE PROCEDURE trg_cdc();

表名称必须像用户输入一样对待。使用quote_ident()来防御SQL注入。

但是，通过这种方式，您可以为每个触发事件创建并删除一个函数。相当大的开销，我不会这么做。您将不得不经常清理一些目录表。

中间立场

PostgreSQL 支持函数重载。因此，每个表的一个具有相同基本名称（但参数类型不同）的函数可以共存。您可以采取中间立场，通过在创建触发器的同时为每个表创建一次 f_cdc(..) 来显着减少噪音。这是每张桌子的一个小功能。您必须观察表定义的更改，但表不应经常更改。从触发器函数中删除CREATE和DROP FUNCTION，得到一个小、快速、优雅的触发器。

我可以在第 8.2 页中看到自己这样做。除了我无法再在 8.2 pg 中看到自己做任何事情。它已于 2011 年 12 月结束。也许你毕竟可以以某种方式升级。

Answer 2

几年前我也问过类似的问题。

看看这个问题，看看它是否给你任何有用的想法：

使用 PL/pgsql 中的 EXECUTE 从通用触发器插入 NEW.*