如何在Postgres中使用“ for语句”触发器？ [英] How to use 'for statement' triggers in postgres?

问题描述

如果我们像下面那样编写一个FOR STATEMENT触发器，我们如何只访问触发器过程/函数中的更新行

if we write a trigger FOR STATEMENT like below how can we access only updated rows in trigger procedure/function

CREATE FUNCTION func1()
RETURNS trigger AS $$
BEGIN
    --access only updated/inserted rows here???
    RETURN null;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trig1
AFTER UPDATE OR DELETE OR INSERT
ON tbl1 FOR STATEMENT
EXECUTE PROCEDURE func1();

我的意思是当有多个行像下面一样更新一次

I mean when there are multiple rows updated once like below

update tbl1 set col1=1 where col2 in (2,3,4,5,6)

推荐答案

目前不支持 NEW 和 OLD 伪关系，用于用于每个语句触发器。

At this time there is no support for NEW and OLD pseudo-relations for FOR EACH STATEMENT triggers.

您必须使用用于每个行触发器。

从理论上讲，PostgreSQL可以为新旧版本的伪造表行版本，通过一次性生成的密钥连接在一起。或一个包含新元组和旧元组作为组合类型的表。但是，目前尚不支持此功能，据我所知没有人对此提供支持。

In theory it's possible for PostgreSQL to have fake tables for the new- and old- row versions, connected together by some one-off generated key. Or a single table containing the new and old tuples as composite types. However, this is not currently supported, and as far as I know nobody is working on support for it.

对于某些应用程序，值得使用 FOR EACH ROW 触发将 INSERT 插入到 TEMPORARY 表中，然后处理全部在最终的每个声明 触发器中。

For some applications it can be worth using FOR EACH ROW triggers to INSERT into a TEMPORARY table, then process the whole lot in a final FOR EACH STATEMENT trigger.

这篇关于如何在Postgres中使用“ for语句”触发器？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！