I have many trigger functions written in PL/pgSQL that follow the below logic:

SELECT my_name
INTO l_my_name
FROM data
WHERE data.my_number = NEW.test_num;

   INSERT INTO test_event
   VALUES (NEW.test_num, NULL, NEW.event_one, NEW.event_two);
   INSERT INTO test_event
   VALUES (NEW.test_num, l_my_name, NEW.event_one, NEW.event_two);

Some of the inserts have upwards of 15+ values and I am trying to simplify them down to just 1 insert as the above code repeats over and over for different checks in the stored procedure needlessly making the procedure hundreds of lines of code long. My thought was something along the lines of:

INSERT INTO test_event
VALUES (NEW.test_num, NULLIF(l_my_name,''), NEW.event_one, NEW.event_two);

I am unsure if l_my_name would be '' if not found and the NULLIF would work properly or if I would have to do something different.

  • See if this helps.
    – mustaccio
    Commented Aug 23, 2022 at 17:35
  • Why not use insert on conflict instead?
    – user1822
    Commented Aug 23, 2022 at 19:32
  • 1
    Aside 1: "psql" is the name of the project's iterminal-based front-end, and not short for PostgreSQL. That would be "Postgres". Maybe "pg", if every letter is precious. Aside 2: Postgres 9.5 has reached EOL in 2021. Upgrade! Commented Aug 23, 2022 at 23:21

1 Answer 1


Simplify to:

INSERT INTO test_event (col1, col2, col3, col4)  -- your actual column names ①
VALUES (NEW.test_num
      , (SELECT my_name FROM data WHERE data.my_number = NEW.test_num)  -- ②
      , NEW.event_one, NEW.event_two);

① Spell out target columns. In persisted code, that's highly advisable, or it will break silently after schema changes to table text_event.

② If the row in table data is found, data.my_name is inserted. "No row" from the subquery is converted to NULL - just what you need. There is no empty string ('') involved here - unless data.my_name happens to be ''.
(Of course, the subquery cannot return more than one row, or an exception is raised.)

That simplifies the logic you displayed. I would have to see your complete table definition, trigger, and trigger function to tell if the operation makes sense to begin with.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.