Database Reference
In-Depth Information
Data cleaning
We notice that employee names don't have consistent cases. It would be easy to en-
force 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 up-
percase:
postgres=# SELECT set_salary('arnold',80);
-[ RECORD 1 ]-------------------
set_salary | INSERTED USER arnold
As 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 ]---
