官术网_书友最值得收藏!

Data cleaning

In the preceding code, we notice that employee names don't have consistent cases. It will be easy to enforce consistency by adding a constraint, as shown here:

CHECK (emp_name = upper(emp_name))

However, it is even better to just make sure that the name is stored as uppercase, and the simplest way to do this 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 arnold

As the uppercasing happens inside a trigger, the function's response still shows a lowercase name, but in the database, it is uppercased:

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 employee names, we can make sure that all employee names will be uppercased 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 will make sense to define a new type – say u_text, which is always stored as uppercase. You will learn more about this approach in Chapter 14, PostgreSQL as Extensible RDBMS.

主站蜘蛛池模板: 娄烦县| 闽侯县| 内丘县| 巴林右旗| 牡丹江市| 玉树县| 垫江县| 兴化市| 太原市| 白河县| 江安县| 邹城市| 阿拉善右旗| 营山县| 蒙阴县| 耿马| 汝城县| 华宁县| 阿拉善盟| 武冈市| 巴楚县| 台州市| 马龙县| 正镶白旗| 永定县| 远安县| 阿坝县| 手游| 当雄县| 出国| 东丰县| 即墨市| 赣州市| 三穗县| 临夏市| 江孜县| 邵阳县| 固镇县| 抚远县| 南平市| 隆化县|