0

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();
  • @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? – PyMapr Jul 6 '16 at 13:03
  • Ah, sorry. I somehow overlooked the WHERE (and I assumed the trigger would be a row level trigger) – a_horse_with_no_name Jul 6 '16 at 13:07
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';

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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