O'Reilly logo

PostgreSQL Server Programming by Jim Mlodgenski, Kirk Roybal, Hannu Krosing

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

Data cleaning

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 ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required