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

Within a trigger function, how to get which fields are being updated

提问by EvilAmarant7x

Is this possible? I'm interested in finding out which columns were specified in the UPDATErequest 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 UPDATErequest doesn't have an identifier so I can substitute in a default value.

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

采纳答案by Erwin Brandstetter

If a "source" doesn't "send an identifier", the column will be unchanged. Then you cannot detect whether the current UPDATEwas 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.

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

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

如果“来源”可以通过任何会话信息功能识别，您就可以使用它。喜欢：

NEW.column = session_user;

Unconditionally for every update.

无条件为每次更新。

General Solution

通用解决方案

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

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

Key element is a per-column triggerintroduced in PostgreSQL 9.0 - a column-specific trigger using the UPDATE OFcolumn_nameclause.

关键元素是PostgreSQL 9.0 中引入的per-column 触发器- 使用UPDATE OFcolumn_name子句的列特定触发器。

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

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

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.

这是我发现的唯一一种区分列是使用与旧值相同的新值更新还是根本没有更新的简单方法。

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

一个可能还通过解析返回的文本current_query()。但这似乎很棘手且不可靠。

Trigger functions

触发功能

I assume a column coldefined NOT NULL.

我假设col定义了一个列NOT NULL。

Step 1:Set colto NULLif unchanged:

第 1 步：如果未更改col，NULL则设置为：

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;

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

第 2 步：恢复到旧值。只有在值实际更新时才会触发触发器（见下文）：

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;

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

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

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;

Triggers

触发器

The trigger for Step 2is fired per column!

每列触发第 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 namesare relevant, because they are fired in alphabetical order (all being BEFORE UPDATE)!

触发器名称是相关的，因为它们是按字母顺序触发的（都是BEFORE UPDATE）！

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

该过程可以通过类似“非列触发器”或任何其他方式来检查触发器中的目标列表来简化UPDATE。但我看不出这有什么办法。

If colcan be NULL, use any other "impossible" intermediate value and check for NULLadditionally in trigger function 1:

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

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

Adapt the rest accordingly.

相应地调整其余部分。

回答by Demian Martinez

Another way is to exploit JSON/JSONB functions that come in recent versions of PostgreSQL. It has the advantage of working both with anything that can be converted to a JSON object (rows or any other structured data), and you don't even need to know the record type.

另一种方法是利用最新版本的 PostgreSQL 中的 JSON/JSONB 函数。它的优点是可以处理任何可以转换为 JSON 对象（行或任何其他结构化数据）的内容，而且您甚至不需要知道记录类型。

To find the differences between any two rows/records, you can use this little hack:

要查找任何两行/记录之间的差异，您可以使用这个小技巧：

SELECT pre.key AS columname, pre.value AS prevalue, post.value AS postvalue
FROM jsonb_each(to_jsonb(OLD)) AS pre
CROSS JOIN jsonb_each(to_json(NEW)) AS post
WHERE pre.key = post.key AND pre.value IS DISTINCT FROM post.value

Where OLDand NEWare the built-in records found in trigger functions representing the pre and after state respectively of the changed record. Note that I have used the table aliases preand postinstead of oldand newto avoid collision with the OLD and NEW built-in objects. Note also the use of IS DISTINCT FROMinstead of a simple !=or <>to handle NULLvalues appropriately.

凡OLD和NEW是内置在代表预触发功能找到的记录和状态分别更改的记录后。请注意，我使用了表别名preandpost而不是oldandnew以避免与 OLD 和 NEW 内置对象发生冲突。还要注意使用 ofIS DISTINCT FROM而不是简单的!=or<>来NULL适当地处理值。

Of course, this will also work with any ROW constructor such as ROW(1,2,3,...)or its short-hand (1,2,3,...). It will also work with any two JSONB objects that have the same keys.

当然，这也适用于任何 ROW 构造函数，例如ROW(1,2,3,...)或其简写(1,2,3,...)。它也适用于任何两个具有相同键的 JSONB 对象。

For example, consider an example with two rows (already converted to JSONB for the purposes of the example):

例如，考虑一个包含两行的示例（出于示例的目的，已经转换为 JSONB）：

SELECT pre.key AS columname, pre.value AS prevalue, post.value AS postvalue
FROM jsonb_each('{"col1": "same", "col2": "prediff", "col3": 1, "col4": false}') AS pre
CROSS JOIN jsonb_each('{"col1": "same", "col2": "postdiff", "col3": 1, "col4": true}') AS post
WHERE pre.key = post.key AND pre.value IS DISTINCT FROM post.value

The query will show the columns that have changed values:

查询将显示已更改值的列：

 columname | prevalue  | postvalue
-----------+-----------+------------
 col2      | "prediff" | "postdiff"
 col4      | false     | true

The cool thing about this approach is that it is trivial to filter by column. For example, imagine you ONLY want to detect changes in columns col1and col2:

这种方法很酷的一点是按列过滤很简单。例如，假设您只想检测列中的更改，col1并且col2：

SELECT pre.key AS columname, pre.value AS prevalue, post.value AS postvalue
FROM jsonb_each('{"col1": "same", "col2": "prediff", "col3": 1, "col4": false}') AS pre
CROSS JOIN jsonb_each('{"col1": "same", "col2": "postdiff", "col3": 1, "col4": true}') AS post
WHERE pre.key = post.key AND pre.value IS DISTINCT FROM post.value
AND pre.key IN ('col1', 'col2')

The new results will exclude col3from the results even if it's value has changed:

col3即使新结果的值发生了变化，它也会从结果中排除：

 columname | prevalue  | postvalue
-----------+-----------+------------
 col2      | "prediff" | "postdiff"

It is easy to see how this approach can be extended in many ways. For example, say you want to throw an exception if certain columns are updated. You can achieve this with a universaltrigger function, that is, one that can be applied to any/all tables, without having to know the table type:

很容易看出如何以多种方式扩展这种方法。例如，假设您想在某些列更新时抛出异常。您可以使用通用触发器函数来实现这一点，即可以应用于任何/所有表的函数，而无需知道表类型：

CREATE OR REPLACE FUNCTION yourschema.yourtriggerfunction()
RETURNS TRIGGER AS
$$
DECLARE
    immutable_cols TEXT[] := ARRAY['createdon', 'createdby'];
BEGIN

    IF TG_OP = 'UPDATE' AND EXISTS(
        SELECT 1
        FROM jsonb_each(to_jsonb(OLD)) AS pre, jsonb_each(to_jsonb(NEW)) AS post
        WHERE pre.key = post.key AND pre.value IS DISTINCT FROM post.value
        AND pre.key = ANY(immutable_cols)
    ) THEN
        RAISE EXCEPTION 'Error 12345 updating table %.%. Cannot alter these immutable cols: %.',
            TG_TABLE_SCHEMA, TG_TABLE_NAME, immutable_cols;
    END IF;

END
$$
LANGUAGE plpgsql VOLATILE

You would then register the above trigger function to any and all tables you want to control via:

然后，您可以通过以下方式将上述触发器函数注册到您想要控制的任何和所有表：

CREATE TRIGGER yourtiggername
BEFORE UPDATE ON yourschema.yourtable
FOR EACH ROW EXECUTE PROCEDURE yourschema.yourtriggerfunction();

回答by Frank Heikens

In plpgsql you could do something like this in your trigger function:

在 plpgsql 中，您可以在触发器函数中执行以下操作：

IF NEW.column IS NULL THEN
  NEW.column = 'default value';
END IF;

回答by mas.morozov

I have obtained another solution to similar problem almost naturally, because my table contained a column with semantics of 'last update timestamp' (lets call it UPDT).

我几乎很自然地获得了类似问题的另一种解决方案，因为我的表包含一个语义为“上次更新时间戳”的列（我们称之为 UPDT）。

So, I decided to include new values of source and UPDT in any update only at once (or none of them). Since UPDT is intended to change on every update, with such a policy one can use condition new.UPDT = old.UPDTto deduce that no source was specified with current update and substitute the default one.

所以，我决定在任何更新中只包含一次（或都不包含） source 和 UPDT 的新值。由于 UPDT 旨在在每次更新时更改，因此可以使用这样的策略new.UPDT = old.UPDT来推断当前更新没有指定源并替换默认源。

If one already has 'last update timestamp' column in his table, this solution will be simpler, than creating three triggers. Not sure if it is better idea to create UPDT, when it is not needed already. If updates are so frequent that there is risk of timestamp similarity, a sequencer can be used instead of timestamp.

如果他的表中已经有“上次更新时间戳”列，则此解决方案将比创建三个触发器更简单。不确定在不需要时创建 UPDT 是否更好。如果更新过于频繁以至于存在时间戳相似的风险，则可以使用排序器代替时间戳。