要忽略PostgreSQL触发之前的结果? [英] To ignore result in BEFORE TRIGGER of PostgreSQL?

问题描述

此线程是该线程的部分挑战，我正在通过TEFORE TRIGGER为其寻找一种更好的解决方案. 我只想启动触发器以将其转换为正确的括号. 我在考虑是否应该从触发器NULL或触发器之前的其他内容中返回.

This thread is a part challenge of this thread to which I am searching a better solution for one part by BEFORE TRIGGER. I just want to launch a trigger to convert to correct brackets. I am thinking whether I should return from the trigger NULL or something else in before trigger.

代码

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 an BEFORE trigger TODO right?，因为我认为在AFTER触发器中是这种情况，但在触发器之前不是这种情况.
我只想启动触发器以转换正确的括号.

I am unsure about this line: RETURN NULL; -- result ignored since this is anBEFOREtrigger TODO right?, since I think this is the case in AFTER trigger but not in before trigger.
I just want to launch a trigger to convert correct brackets.

测试命令是sudo -u postgres psql detector -c "SELECT f_create_my_trigger_events(1,'[112]','[113]');"，由于对返回值的误解而导致出现以下错误.

Test command is sudo -u postgres psql detector -c "SELECT f_create_my_trigger_events(1,'[112]','[113]');" getting the following error because of misunderstanding of the returning -thing, I think.

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触发器?

How can you manage BEFORE triggers in PostgreSQL 9.4?

推荐答案

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

First of all, you need to pass the row variable in a BEFORE trigger. Passing NULL cancels the operation for the row:

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 ...

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

我在上一个答案中展示了RETRUN NULL在AFTER触发器中的用法.您不能对BEFORE触发器执行相同的操作. 文档:

I demonstrated the use of RETRUN NULL in an AFTER trigger in my previous answer. You can't do the same for a BEFORE trigger. The documentation:

行级触发器已触发BEFORE可以返回null表示该触发器 经理跳过此行的其余操作(即， 随后的触发器不会触发，而INSERT/UPDATE/DELETE会触发 此行不会发生).如果返回非空值，则 该行值继续操作.

Row-level triggers fired BEFORE can return null to signal the trigger manager to skip the rest of the operation for this row (i.e., subsequent triggers are not fired, and the INSERT/UPDATE/DELETE does not occur for this row). If a nonnull value is returned then the operation proceeds with that row value.

还有更多.阅读手册.

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

But since you are passing two 1-dimensional arrays instead of one 2-dimensional array now, you need to adapt your trigger logic:

CREATE OR REPLACE FUNCTION insbef_events_function() 
  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$  LANGUAGE plpgsql;

现在，您有责任使两个数组具有相同数量的元素.
更改触发器的函数现在看起来像这样:

Now it's your responsibility that both arrays have the same number of elements.
The function changing the trigger could look like this now:

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 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$  LANGUAGE plpgsql;

在使用此高级自动化设计之前，您需要了解SQL，plpgsql和触发器函数的基础.

You need to understand basics of SQL, plpgsql and trigger functions before using this advanced automated design.

这篇关于要忽略PostgreSQL触发之前的结果?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！