INSERT from trigger not happening?

Question

I'm new to triggers in PostgreSQL.
I have 3 tables in this example table1, table2 and table3.

• The trigger fires on new record on table1.
• The trigger function loops through all records of table2 that have the same product_id as the new record from table1.
• It inserts into table3 some mixed values from the first 2 tables.
• After that, the same INSERT query is inserted into a test table for testing purpose.

The problem is the INSERT on table3 is not happening. The INSERT on test table is fine and the logged insert query can be executed without troubles, so I don't know why it's not executed inside the trigger/function.

CREATE OR REPLACE FUNCTION my_trigger() RETURNS TRIGGER AS $my_trigger$
DECLARE r RECORD;
BEGIN
  FOR r IN SELECT t2.id_t2, t2.name_1, t2.name_2, t2.name_3 FROM table2 t2 WHERE t2.product_id=NEW.product_id 
  LOOP
    EXECUTE 'INSERT INTO table3 (id_t3, id_t1, name_1, name_2, name_3, bool_t2) VALUES (' || r.id_t2 || ',' || NEW.id_t1 || ', ''' || r.name_1 || ''',''' || r.name_2 || ''',''' || r.name_3 || ''', TRUE);';
    INSERT INTO test (field1, field2) VALUES(r.id_t2, 'INSERT INTO table3 (id_t3, id_t1, name_1, name_2, name_3, bool_t2) VALUES (' || r.id_t2 || ',' || NEW.id_t1 || ', ''' || r.name_1 || ''',''' || r.name_2 || ''',''' || r.name_3 || ''', TRUE);');
  END LOOP;
RETURN NEW;
END;
$my_trigger$ LANGUAGE plpgsql;

Edit: As @Rachcha asked, the trigger itself is defined this way:

CREATE TRIGGER my_trigger
  AFTER INSERT
  ON table1
  FOR EACH ROW
  EXECUTE PROCEDURE my_trigger();

Edit 2: I've also tried to insert without EXECUTE, but the result is the same: no errors but no fields inserted into table3.

Answer 1

Simpler function

It makes no sense to RETURN NEW in an AFTER trigger. Use RETURN NULL instead. The manual:

The return value is ignored for row-level triggers fired after an operation, and so they can return NULL.

No point in using a LOOP. Use a simple SQL statement instead.
No point in using dynamic SQL with EXECUTE.
This should work:

CREATE OR REPLACE FUNCTION my_trigger()
  RETURNS trigger
  LANGUAGE plpgsql AS
$func$
BEGIN
   INSERT INTO table3
         (   id_t3,     id_t1,    name_1, bool_t2)
   SELECT t2.id_t2, NEW.id_t1, t2.name_1, true
   FROM   table2 t2
   WHERE  t2.product_id = NEW.product_id;

   INSERT INTO test
         (  field1, field2)
   SELECT t2.id_t2, 'INSERT ...'
   FROM   table2 t2
   WHERE  t2.product_id = NEW.product_id;

   RETURN NULL;
END
$func$;

Or SELECT from table2 once in a CTE and use that in multiple INSERT commands:

...
   WITH t2 AS (
      SELECT t.id_t2, t.name_1
      FROM   table2 t
      WHERE  t.product_id = NEW.product_id
      )
   , ins_t3 AS (
      INSERT INTO table3
            (   id_t3,     id_t1,    name_1, bool_t2)
      SELECT t2.id_t2, NEW.id_t1, t2.name_1, true
      FROM   t2
      )
   INSERT INTO test
         (  field1, field2)
   SELECT t2.id_t2, 'INSERT ...'
   FROM   t2;
...

fiddle
_{Old sqlfiddle}

Debugging

If it doesn't work, the problem is something that is not in your question. Do you have any other triggers or rules defined on table1 or table test?

To debug, add this line to your trigger function to see if you even get there and what values are in NEW

RAISE EXCEPTION 'Values in NEW: %', NEW::text;