gpt4 book ai didi

sql - 如何在 PostgreSQL 8.2 中动态使用 TG_TABLE_NAME?

转载 作者:行者123 更新时间:2023-11-29 11:23:23 28 4
gpt4 key购买 nike

我正在尝试在 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_”,因为所有表都遵循相同的命名约定。然后我可以让每个表的每个触发器只调用一个函数。

最佳答案

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

Since PostgreSQL 8.4你可以:

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

对于 pg 8.2,你有一个问题:

  • 无法动态访问 NEW/OLD 的列。你得知道编写触发函数时的列名。
  • NEW/OLDEXECUTE 中不可见。
  • 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 注入(inject)。

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

中间地带

PostgreSQL 支持 function overloading .因此,相同基本名称(但参数类型不同)的每个表的一个函数可以共存。您可以采取中间立场并通过在创建触发器的同时为每个表创建一次 f_cdc(..) 来显着降低噪音。这是每个表的一个小功能。您必须观察表定义的变化,但表不应该经常变化。从触发器函数中删除 CREATEDROP FUNCTION,得到一个小巧、快速且优雅的触发器。

我可以看到自己在 pg 8.2 中这样做。除了我再也看不到自己在 pg 8.2 中做任何。它有 reached end of life in December 2011 .也许您毕竟可以以某种方式升级。

关于sql - 如何在 PostgreSQL 8.2 中动态使用 TG_TABLE_NAME?,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/7519044/

28 4 0
文章推荐: iphone - 复制(复制)核心数据记录和相关记录
文章推荐: mysql - Node js mysql 包装器错误 - 对象 # 没有方法 'table'