Postgresql – how to delete all rows with empty field via function/trigger combo in Postgres v 9.3

plpgsqlpostgresqltrigger

In PostgreSQL 9.3 I am trying to write a trigger function that will be called after updates to table test2 and that will delete rows of test2 where field "email" is empty. Here's my attempt:

CREATE OR REPLACE FUNCTION clean_emp() RETURNS trigger AS                                                                                                                                                                         
$$
BEGIN
DELETE FROM test2 WHERE email=null;
return test2;
END;
$$
LANGUAGE plpgsql VOLATILE;

Then I set up the trigger like so:

CREATE TRIGGER trig_empty2
AFTER UPDATE OF name ON test2 FOR EACH ROW EXECUTE PROCEDURE clean_emp();

This gives me an error when I then do an update on table test2:

update test2 set name = name || 'fesgses';
ERROR:  column "test2" does not exist

And here's my data table for completeness:

  name  |    email
--------+-------------
 hi     | hiemail
 shoe   |
 shoe2  |
 cathy  |

If anyone can give me some pointers on where I'm going wrong, I'd really appreciate it. Thank you.

Best Answer

There are several things wrong with this trigger.

First: your delete statement. You can't compare NULL using =. You need to use IS NULL:

DELETE FROM test2 WHERE email IS NULL;

Second: a trigger function (quote from the manual) "must return either NULL or a record/row value having exactly the structure of the table the trigger was fired for."

So return test2; should be return new;.

Third: you created a row level trigger, which is going to be very bad for performance. As you are not dependent on the actual values that are changed by the update, a statement level trigger will be much more efficient, because it only fires once for each UPDATE statement rather than once for each row that has been changed.

In a statement level trigger the return value is ignored, so the suggested return new; from above becomes a return null; as there is no new or old record available in a statement level trigger.

Putting it all together: 

CREATE OR REPLACE FUNCTION clean_emp() 
  RETURNS trigger AS                                                                           $$
BEGIN
  DELETE FROM test2 WHERE email IS NULL;
  return NULL; -- as we are now using a statement level trigger, null is fine
END;
$$
LANGUAGE plpgsql VOLATILE;

CREATE TRIGGER trig_empty2
AFTER UPDATE OF name ON test2 
  FOR EACH STATEMENT --<< fire only once for each statement, not row
  EXECUTE PROCEDURE clean_emp();

If you also want to disallow empty values ( '' is something different than 'NULL') you would need to change the condition to where coalesce(email, '') = '')

But the trigger approach is wrong to begin with: you should declare the email column as NOT NULL and then nobody will ever be able to put NULL values into that column and therefore you don't need the trigger at all.

Related Question