对于每个语句触发器示例

Question

我一直在看postgresql触发器的文档，但它似乎只显示了行级触发器的示例，但我找不到语句级触发器的示例。

特别是，如何在单个语句中迭代更新/插入行不是很清楚，因为NEW是针对单个记录的。

Answer 1

OLD和NEW为空或未在语句级触发器中定义。每个文档:

NEW

数据类型RECORD;变量保存行级触发器中INSERT/UPDATE操作的新数据库行。这个变量是语句级触发器和DELETE操作

OLD

数据类型RECORD;为行级触发器中的UPDATE/DELETE操作保存旧数据库行的变量。该变量在语句级触发器和INSERT操作中为空。

粗体强调我的

到Postgres 10，这个读起来略有不同，但效果大致相同:

…该变量在语句级触发器中未赋值 . ...

虽然这些记录变量对于语句级触发器仍然没有用处，但有一个新特性非常重要:

Postgres 10+中的过渡表

Postgres 10引入了转换表。它们允许访问所有受影响的行。手动:

AFTER 触发器还可以使用转换表来检查触发语句更改的整个行集。CREATE TRIGGER命令为一个或两个转换分配名称表，然后函数可以引用这些名称，就好像它们是只读临时表。例43.7给出了一个示例。

按照手册的链接获取代码示例。

示例语句级触发器不包含转换表

在过渡表出现之前，这些甚至更不常见。一个有用的例子是在某些DML命令之后发送通知。
以下是我使用的基本版本:

-- Generic trigger function, can be used for multiple triggers:
CREATE OR REPLACE FUNCTION trg_notify_after()
  RETURNS trigger
  LANGUAGE plpgsql AS
$func$
BEGIN
   PERFORM pg_notify(TG_TABLE_NAME, TG_OP);
   RETURN NULL;
END
$func$;
-- Trigger
CREATE TRIGGER notify_after
AFTER INSERT OR UPDATE OR DELETE ON my_tbl
FOR EACH STATEMENT
EXECUTE PROCEDURE trg_notify_after();

对于Postgres 11或更高版本，使用相同的，更容易混淆的语法:

...
EXECUTE FUNCTION trg_notify_after();

:

• 触发功能不存在，但我很确定它确实存在

Answer 2

下面是一些语句级触发器的例子。

表:

CREATE TABLE public.test (
    number integer NOT NULL,
    text   character varying(50)
);

触发功能:
OLD和NEW仍然是NULL
返回值也可以一直留在NULL。

CREATE OR REPLACE FUNCTION public.tr_test_for_each_statement()
   RETURNS trigger
   LANGUAGE plpgsql
AS
$$
DECLARE
    x_rec record;
BEGIN
    raise notice '=operation: % =', TG_OP;
    IF (TG_OP = 'UPDATE' OR TG_OP = 'DELETE') THEN
        FOR x_rec IN SELECT * FROM old_table LOOP
            raise notice 'OLD: %', x_rec;
        END loop;
    END IF;
    IF (TG_OP = 'INSERT' OR TG_OP = 'UPDATE') THEN
        FOR x_rec IN SELECT * FROM new_table LOOP
            raise notice 'NEW: %', x_rec;
        END loop;
    END IF;
    RETURN NULL;
END;
$$;

设置语句级触发器
只支持AFTER和一个事件。

CREATE TRIGGER tr_test_for_each_statement_insert
   AFTER INSERT ON public.test
   REFERENCING NEW TABLE AS new_table
   FOR EACH STATEMENT
   EXECUTE PROCEDURE public.tr_test_for_each_statement();
CREATE TRIGGER tr_test_for_each_statement_update
   AFTER UPDATE ON public.test
   REFERENCING NEW TABLE AS new_table OLD TABLE AS old_table
   FOR EACH STATEMENT
   EXECUTE PROCEDURE public.tr_test_for_each_statement();
CREATE TRIGGER tr_test_for_each_statement_delete
   AFTER DELETE ON public.test
   REFERENCING OLD TABLE AS old_table
   FOR EACH STATEMENT
   EXECUTE PROCEDURE public.tr_test_for_each_statement();

例子:

INSERT INTO public.test(number, text) VALUES (1, 'a');

= INSERT =
新:(1)

INSERT INTO public.test(number, text) VALUES (2, 'b'),  (3, 'b');

= INSERT =
NEW: (2,b)
新:(3 b)

UPDATE public.test SET number = number + 1 WHERE text = 'a';

=operation: UPDATE =
(1,a)
新:(2)

UPDATE public.test SET number = number + 10 WHERE text = 'b';

=operation: UPDATE =
(2,b)
(3,b)
NEW: (12,b)
新:(13 b)

DELETE FROM public.test;

=operation: DELETE =
(2,a)
OLD: (12,b)
老:(13,b)