如何在 PostgreSQL 8.2 中动态使用 TG_TABLE_NAME? [英] How to dynamically use TG_TABLE_NAME in PostgreSQL 8.2?

问题描述

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

I am trying to write a trigger function in PostgreSQL 8.2 that will dynamically use TG_TABLE_NAME to generate and execute a SQL statement. I can find all kinds of examples for later versions of PostgreSQL, but I am stuck on 8.2 because of some requirements. Here is my function as it stands which works, but is hardly dynamic:

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_' 前缀，因为所有表都遵循相同的命名约定.然后我可以让每个表的每个触发器只调用一个函数.

The way this is currently written, I would have to write a separate trigger function for every table. I would like to use TG_TABLE_NAME to dynamically build my INSERT statement and just prefix it with 'cdc_' since all of the tables follow the same naming convention. Then I can have every trigger for every table call just one function.

推荐答案

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

I was looking for the exact same thing a couple of years back. One trigger function to rule them all! I asked on usenet lists, tried various approaches, to no avail. The consensus on the matter was this could not be done. A shortcoming of PostgreSQL 8.3 or older.

自 PostgreSQL 8.4 你可以:

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

使用 pg 8.2 时会遇到问题:

With pg 8.2 you have a problem:

• 无法动态访问NEW/OLD 的列.你得知道编写触发器函数时的列名.
• NEW/OLD 在 EXECUTE 中不可见.
• EXECUTE .. USING 还没出生.

• cannot dynamically access columns of NEW / OLD. You need to know column names at the time of writing the trigger function.
• NEW / OLD are not visible inside EXECUTE.
• EXECUTE .. USING not born yet.

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

Every table name in the system can serve as composite type of the same name. Therefore you can create a function that takes NEW / OLD as parameter and execute that. You can dynamically create and destroy that function on every trigger event:

触发功能:

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 注入.

Table names have to be treated like user input. Use quote_ident() to defend against SQL injection.

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

However, this way you create and drop a function for every single trigger event. Quite an overhead, I would not go for that. You will have to vacuum some catalog tables a lot.

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

PostgreSQL supports function overloading. Therefore, one function per table of the same base name (but different parameter type) can coexist. You could take the middle ground and dramatically reduce the noise by creating f_cdc(..) once per table at the same time you create the trigger. That's one tiny function per table. You have to observe changes of table definitions, but tables shouldn't change that often. Remove CREATE and DROP FUNCTION from the trigger function, arriving at a small, fast and elegant trigger.

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

I could see myself doing that in pg 8.2. Except that I cannot see myself doing anything in pg 8.2 anymore. It has reached end of life in December 2011. Maybe you can upgrade somehow after all.

这篇关于如何在 PostgreSQL 8.2 中动态使用 TG_TABLE_NAME?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！