To ignore result in BEFORE TRIGGER of PostgreSQL?

Question

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.

Code

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;

which I use the by the function

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;

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.

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

How can you manage BEFORE triggers in PostgreSQL 9.4?

Middaugh

@ErwinBrandstetter Yes, yours! I took some days in testing your suggestions in different settings and they worked but forgot to accept your answer. – Chenopod

Answer 1

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;

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 manual:

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.

There is more. Read the manual.

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() 
    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$;

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)
  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$;

You need to understand basics of SQL, PL/pgSQL, trigger functions and array handling before using this advanced automated design.

Voncile