Simplify duplicate INSERT commands placing NULL for values that are not found

Question

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;
IF NOT FOUND THEN
 INSERT INTO test_event
 VALUES (NEW.test_num, NULL, NEW.event_one, NEW.event_two);
ELSE 
 INSERT INTO test_event
 VALUES (NEW.test_num, l_my_name, NEW.event_one, NEW.event_two);
END IF;

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.

• mustaccio: See if this helps.
• user1822: Why not use insert on conflict instead?
• Erwin Brandstetter: 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!

Answer 1

Simplify to:

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

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

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