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
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 ();
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 ...