在触发函数中，如何获取要更新的字段 [英] Within a trigger function, how to get which fields are being updated

问题描述

这可能吗？我有兴趣找出在 UPDATE 请求中指定了哪些列，而不考虑发送的新值可能是也可能不是存储在

Is this possible? I'm interested in finding out which columns were specified in the UPDATE request regardless of the fact that the new value that is being sent may or may not be what is stored in the database already.

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

The reason I want to do this is because we have a table that can receive updates from multiple sources. Previously, we weren't recording which source the update originated from. Now the table stores which source has performed the most recent update. We can change some of the sources to send an identifier, but that isn't an option for everything. So I'd like to be able to recognize when an UPDATE request doesn't have an identifier so I can substitute in a default value.

推荐答案

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

If a "source" doesn't "send an identifier", the column will be unchanged. Then you cannot detect whether the current UPDATE was done by the same source as the last one or by a source that did not change the column at all. In other words: this does not work properly.

如果任何会话信息功能，您可以使用它。像这样：

If the "source" is identifiable by any session information function, you can work with that. Like:

NEW.column = session_user;

每次更新均无条件。

我找到了一种解决原始问题的方法。在任何更新中，该列将设置为默认值，其中该列未更新（不在 SET 中） UPDATE 的列表。）

I found a way how to solve the original problem. The column will be set to a default value in any update where the column is not updated (not in the SET list of the UPDATE).

关键元素是 每列触发器 -使用<$的特定于列的触发器c $ c>更新 列名 子句。

Key element is a per-column trigger introduced in PostgreSQL 9.0 - a column-specific trigger using the UPDATE OFcolumn_name clause.

仅当列出的列中至少有一个被列为
作为 UPDATE 命令的目标时，才会触发触发器。

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

这是我发现区分一列是否使用与旧值相同的新值进行更新而不是根本不更新的唯一简单方法。

That's the only simple way I found to distinguish whether a column was updated with a new value identical to the old, versus not updated at all.

一个可以还可解析 current_query（） 。但这似乎很棘手且不可靠。

One could also parse the text returned by current_query(). But that seems tricky and unreliable.

我假设一列 col 定义为 NOT NULL 。

步骤1：设置 col 到 NULL （如果未更改）：

Step 1: Set col to NULL if unchanged:

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

   RETURN NEW;
END
$func$  LANGUAGE plpgsql;

步骤2：还原为旧值。如果值实际上已更新，则将仅触发该触发器（见下文）：

Step 2: Revert to old value. Trigger will only be fired, if the value was actually updated (see below):

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

   RETURN NEW;
END
$func$  LANGUAGE plpgsql;

步骤3：现在，我们可以确定缺少的更新并设置默认值取而代之的是值：

Step 3: Now we can identify the lacking update and set a default value instead:

CREATE OR REPLACE FUNCTION trg_tbl_upbef_step3()
  RETURNS trigger AS
$func$
BEGIN
   IF NEW.col IS NULL THEN
      NEW.col := 'default value';
   END IF;

   RETURN NEW;
END
$func$  LANGUAGE plpgsql;

触发器

的触发器>步骤2 每列触发！

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

CREATE TRIGGER upbef_step2
  BEFORE UPDATE OF col 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();

触发器名称是相关的，因为它们是按字母顺序触发的（所有都是更新前）！

Trigger names are relevant, because they are fired in alphabetical order (all being BEFORE UPDATE)!

可以使用诸如非列触发器之类的方法或其他任何检查 UPDATE的目标列表的方法来简化该过程。 在触发器中。

The procedure could be simplified with something like "per-not-column triggers" or any other way to check the target-list of an UPDATE in a trigger. But I see no handle for this.

如果 col 可以为 NULL ，使用任何其他不可能的中间值，并在触发函数1中另外检查 NULL ：

If col can be NULL, use any other "impossible" intermediate value and check for NULL additionally in trigger function 1:

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

相应地调整其余部分。

这篇关于在触发函数中，如何获取要更新的字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！