在PostgreSQL的BEFORE TRIGGER中如何忽略结果？

Question

这个帖子是这个帖子挑战的一部分，我正在寻找使用BEFORE TRIGGER更好的解决方案。 我只想启动一个触发器来转换为正确的括号。 我在考虑在before trigger中是否应该返回NULL或其他内容。

代码

CREATE OR REPLACE FUNCTION insbef_events_function() 
    RETURNS TRIGGER AS 
$func$
DECLARE
    m int[]; 
BEGIN
   FOREACH m SLICE 1 IN ARRAY TG_ARGV[0]::int[]
   LOOP
      INSERT INTO events (measurement_id, event_index_start, event_index_end) 
      SELECT NEW.measurement_id, m[1], m[2];  -- Postgres array starts with 1 !
   END LOOP;

      -- do something with _result ...

RETURN NULL; -- result ignored since this is an BEFORE trigger TODO right?
END 
$func$ LANGUAGE plpgsql;

我使用的函数是

CREATE OR REPLACE FUNCTION f_create_my_trigger_events(_arg1 int, _arg2 text, _arg3 text)
  RETURNS void AS
$func$
BEGIN

EXECUTE format($$
    DROP TRIGGER IF EXISTS insbef_ids ON events
    CREATE TRIGGER insbef_ids
    BEFORE INSERT ON events
    FOR EACH ROW EXECUTE PROCEDURE insbef_events_function(%1$L)$$
    , translate(_arg2, '[]', '{}'), translate(_arg3, '[]', '{}')
);

END
$func$ LANGUAGE plpgsql;

我对这一行不确定：RETURN NULL; -- result ignored since this is anBEFOREtrigger TODO right?，因为我认为这是在AFTER触发器中的情况，而不是在之前的触发器中。
我只是想启动一个触发器来转换正确的括号。

测试命令是sudo -u postgres psql detector -c "SELECT f_create_my_trigger_events(1,'[112]','[113]');"，由于误解返回内容的原因，出现了以下错误。

LINE 3:     CREATE TRIGGER insbef_ids
            ^
QUERY:  
    DROP TRIGGER IF EXISTS insbef_ids ON events
    CREATE TRIGGER insbef_ids
    BEFORE INSERT ON events
    FOR EACH ROW EXECUTE PROCEDURE insbef_events_function('{112}')
CONTEXT:  PL/pgSQL function f_create_my_trigger_events(integer,text,text) line 4 at EXECUTE statement

如何在PostgreSQL 9.4中管理BEFORE触发器？

- Léo Léopold Hertz 준영

---

@ErwinBrandstetter 是的，就是您的建议！我花了几天时间在不同情况下测试了您的建议，它们确实有效，但是我忘记接受您的答案了。 - Léo Léopold Hertz 준영

Answer 1

首先，您需要在 BEFORE 触发器中传递行变量。传递 NULL 将取消该行的操作：

CREATE OR REPLACE FUNCTION insbef_events_function() 
  RETURNS TRIGGER AS 
$func$
DECLARE
   m int[]; 
BEGIN
   FOREACH m SLICE 1 IN ARRAY TG_ARGV[0]::int[]
   LOOP
      INSERT INTO events (measurement_id, event_index_start, event_index_end) 
      SELECT NEW.measurement_id, m[1], m[2];  -- Postgres array subscripts start with 1
   END LOOP;

      -- do something with _result ...

   <b>RETURN NEW;  -- NULL would cancel operation in BEFORE trigger!</b>
END 
$func$ LANGUAGE plpgsql;

我在之前的回答中演示了在AFTER触发器中使用RETRUN NULL。但你不能在BEFORE触发器中做同样的事情。手册。

BEFORE触发的行级触发器可以返回null，以向触发管理器信号跳过此行的其余操作（即，不会触发后续的触发器，并且INSERT/UPDATE/DELETE不会对此行发生）。如果返回非空值，则使用该行值进行操作。

还有更多内容，请查看手册。

但是，由于现在你传递的是两个一维数组而不是一个二维数组，因此需要调整触发器逻辑：

CREATE OR REPLACE FUNCTION insbef_events_function() 
    LANGUAGE plpgsql RETURNS TRIGGER AS 
$func$
DECLARE
   a1 int[] := TG_ARGV[1]::int[];
   a2 int[] := TG_ARGV[2]::int[];
BEGIN
   FOR i in array_lower(a1, 1) .. array_upper(a1, 1)
   LOOP
      INSERT INTO events (measurement_id, event_index_start, event_index_end) 
      SELECT NEW.measurement_id  -- or TG_ARGV[0]::int instead?
           , a1[i], a2[i];
   END LOOP;

   RETURN NEW;  -- NULL would cancel operation in BEFORE trigger!
END 
$func$;

你有责任确保两个数组拥有相同数量的元素。
现在，更改触发器的函数可能看起来像这样：

CREATE OR REPLACE FUNCTION f_create_my_trigger_events(_arg1 int, _arg2 text, _arg3 text)
  LANGUAGE plpgsql RETURNS void AS
$func$
BEGIN
   EXECUTE format(
    $$DROP TRIGGER IF EXISTS insbef_ids ON measurements; -- on measurements ..
      CREATE TRIGGER insbef_ids
      BEFORE INSERT ON measurements  -- .. according to previous posts!!
      FOR EACH ROW EXECUTE PROCEDURE insbef_events_function(%s, %L, %L)$$
    , _arg1
    , translate(_arg2, '[]', '{}')
    , translate(_arg3, '[]', '{}')
   );
END
$func$;

在使用这种高级自动化设计之前，您需要了解SQL、PL/pgSQL、触发器函数和数组处理的基础知识。

- Erwin Brandstetter