sql - 在触发器函数中，如何获取正在更新的字段

Question

这可能吗？我有兴趣找出在 UPDATE 请求中指定了哪些列，而不管正在发送的新值是否已经存储在数据库中。

我想这样做的原因是因为我们有一个可以从多个源接收更新的表。以前，我们没有记录更新的来源。现在该表存储哪个源执行了最近的更新。我们可以更改某些来源以发送标识符，但这并不是对所有情况都适用的选项。所以我希望能够识别 UPDATE 请求何时没有标识符，以便我可以替换为默认值。

Answer 1

如果“来源”不“发送标识符”，则该列将保持不变。然后您无法检测当前 UPDATE 是由与上一个相同的源完成的还是由根本没有更改列的源完成的。换句话说:这不能正常工作。

如果“来源”可由任何 session information function 识别，你可以使用它。喜欢:

NEW.column = session_user;

每次更新无条件

通用解决方案

我找到了解决原始问题的方法。

如果列不是在 UPDATE 中(不在 SET 列表中)，则将其设置为默认值。关键元素是随 PostgreSQL 9.0 引入的每列触发器 - 使用UPDATE OFcolumn_name 条款。 The manual :

The trigger will only fire if at least one of the listed columns ismentioned as a target of the UPDATE command.

这是我发现的区分列是否更新为与旧值相同的新值以及根本没有更新的唯一简单方法。

也可以解析 current_query() 返回的文本.但这似乎很麻烦、棘手且不可靠。

触发函数

我假设列 source 定义了 NOT NULL。

第 1 步:如果未更改，则将 source 设置为 NULL:

CREATE OR REPLACE FUNCTION trg_tbl_upbef_step1()
  RETURNS trigger
  LANGUAGE plpgsql AS
$func$
BEGIN
   IF NEW.source = OLD.source THEN
      NEW.source := NULL;      -- "impossible" value (source is NOT NULL)
   END IF;

   RETURN NEW;
END
$func$;

第 2 步:恢复为旧值。触发器将仅在值实际更新时触发(见下文):

CREATE OR REPLACE FUNCTION trg_tbl_upbef_step2()
  RETURNS trigger
  LANGUAGE plpgsql AS
$func$
BEGIN
   IF NEW.source IS NULL THEN
      NEW.source := OLD.source;
   END IF;

   RETURN NEW;
END
$func$;

第 3 步:现在我们可以识别缺少的更新并改为设置默认值:

CREATE OR REPLACE FUNCTION trg_tbl_upbef_step3()
  RETURNS trigger
  LANGUAGE plpgsql AS
$func$
BEGIN
   IF NEW.source IS NULL THEN
      NEW.source := 'UPDATE default source';  -- optionally same as column default
   END IF;

   RETURN NEW;
END
$func$;

触发器

第 2 步的触发器按列触发!

CREATE TRIGGER upbef_step1
  BEFORE UPDATE ON tbl
  FOR EACH ROW
  EXECUTE PROCEDURE trg_tbl_upbef_step1();

CREATE TRIGGER upbef_step2
  BEFORE UPDATE <b>OF source</b> ON tbl             -- key element!
  FOR EACH ROW
  EXECUTE PROCEDURE trg_tbl_upbef_step2();
    
CREATE TRIGGER upbef_step3
  BEFORE UPDATE ON tbl
  FOR EACH ROW
  EXECUTE PROCEDURE trg_tbl_upbef_step3();

db<> fiddle here

触发器名称是相关的，因为它们是按字母顺序触发的(都是BEFORE UPDATE)!

可以使用类似“per-not-column 触发器”或任何其他方式来简化该过程，以检查触发器中 UPDATE 的目标列表。但是我目前看不到这个的句柄(从 Postgres 14 开始没有改变)。

如果 source 可以是 NULL，则使用任何其他“不可能的”中间值并在触发器函数 1 中额外检查 NULL:

IF OLD.source IS NOT DISTINCT FROM NEW.source THEN
    NEW.source := '#impossible_value#';
END IF;

相应地调整其余部分。