plpgsql function to replace ' ' with null after insert using a trigger

Question

I have a table in PostgreSQL 9.5 on which insert statements are run frequently. After an insert statement was ran on the table there are ' ' (single quote, space, single quote) values when no data was inserted for a specific column. I want to run a trigger function after each insert to replace the ' ' with Null. However, I am struggling with writing the function.

What I have thus far:

CREATE OR REPLACE FUNCTION delete_space() RETURNS TRIGGER AS $_$
BEGIN
UPDATE proj.general
SET deliv1 = Null,
deliv2 = Null,
fmt = Null,
spec_instr = Null
where general.deliv1 = ''
or general.deliv2 = ''
or general.fmt = ''
or general.spec_instr = '';
RETURN NULL;
END $_$ LANGUAGE 'plpgsql';

This clears the ' ' but it also clears any new input in the columns specified in the function. As an example, if 'this is special instructions' is inserted into column general.spec_instr, the input is cleared after the function ran. How do I rectify this? Also, is there a way to check all columns of new inserts for ' ' without having to specify the specific column name as in the function above?

EDIT:

CREATE TRIGGER del_spaces_trig
 AFTER INSERT
 ON projects.general
 FOR EACH STATEMENT
 EXECUTE PROCEDURE public.delete_space();

• PyMapr: @a_horse_with_no_name I am pretty new to postgres and sql. Doesn't the UPDATE statement only update the columns specified with SET and WHERE?
• user1822: Ah, sorry. I somehow overlooked the WHERE (and I assumed the trigger would be a row level trigger)

Answer 1

Just modify your SET clause and use CASE ... WHEN ... END:

CREATE OR REPLACE FUNCTION delete_space() RETURNS TRIGGER AS $_$
BEGIN
UPDATE proj.general
SET deliv1 = case when deliv1 ='' then NULL else deliv1 end,
SET deliv1 = case when deliv2 ='' then NULL else deliv2 end,
SET deliv1 = case when fmt ='' then NULL else fmt end,
SET deliv1 = case when spec_instr ='' then NULL else spec_instr end
where general.deliv1 = ''
or general.deliv2 = ''
or general.fmt = ''
or general.spec_instr = '';
RETURN NULL;
END $_$ LANGUAGE 'plpgsql';