We notice that employee names don't have consistent cases. It would be easy to enforce consistency by adding a constraint:
CHECK (emp_name = upper(emp_name))
However, it is even better to just make sure that it is stored as uppercase, and the simplest way to do it is by using trigger:
CREATE OR REPLACE FUNCTION uppercase_name ()
RETURNS trigger AS $$
BEGIN
NEW.emp_name = upper(NEW.emp_name);
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER uppercase_emp_name
BEFORE INSERT OR UPDATE OR DELETE ON salaries
FOR EACH ROW EXECUTE PROCEDURE uppercase_name ();The next set_salary() call for a new employee will now insert emp_name in uppercase:
postgres=# SELECT set_salary('arnold',80);
-[ RECORD 1 ]-------------------
set_salary | INSERTED USER arnoldAs the uppercasing happened inside a trigger, the function response still shows a lowercase name, but in the database it is uppercase:
postgres=# SELECT * FROM salaries ; -[ RECORD 1 ]--- emp_name | Bob salary | 1300 -[ RECORD 2 ]--- emp_name | Fred salary | 750 -[ RECORD 3 ]--- emp_name | frank salary | 100 -[ RECORD 4 ]--- emp_name | ARNOLD salary | 80
After fixing the existing mixed-case emp_names, we can make sure that all emp_names will be in uppercase in the future by adding a constraint:
postgres=# update salaries set emp_name = upper(emp_name) where not emp_name = upper(emp_name); UPDATE 3 postgres=# alter table salaries add constraint emp_name_must_be_uppercasepostgres-# CHECK (emp_name = upper(emp_name)); ALTER TABLE
If this behavior is needed in more places, it would make sense to define a new type – say u_text, which is always stored as uppercase. You will learn more about this approach in the chapter about defining user types.