logo标签列表

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

sqlpostgresqlfunctiontriggersplpgsql
8

我正在尝试在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_”，因为所有表都遵循相同的命名约定。然后，每个表的每个触发器都可以调用同一个函数。
那你尝试了什么，什么没有起作用？ - Milen A. Radev
如果您只是尝试下一个版本中有效的示例，会发生什么？ - Morg.
下一个版本中的示例失败了。从8.4开始，PLPGSQL进行了一些基本更改。 - Rob
1
你应该真的尝试升级到支持的版本。 - user330315
2个回答
16

几年前我也在寻找完全相同的东西。一个触发函数统治所有！我在usenet列表上问过，尝试了各种方法，但都没有成功。有关此事的共识是这是不可能完成的。这是PostgreSQL 8.3或更早版本的缺陷。

自从PostgreSQL8.4以来，你只需要：

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，得到一个小巧，快速和优雅的触发器。

我可以看到我在pg 8.2中这样做。除了我不再能在pg 8.2中做任何事情。它已经在2011年12月达到生命周期的终点。也许您可以升级。

0
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接