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?
CREATE TRIGGER del_spaces_trig AFTER INSERT ON projects.general FOR EACH STATEMENT EXECUTE PROCEDURE public.delete_space();