27、自定义函数与数据库触发器的应用

自定义函数与数据库触发器的应用

1. 自定义函数简介

在数据处理过程中，我们经常会使用到各种函数，例如 upper() 用于大写字母转换， sum() 用于数值求和。这些函数背后其实是大量（有时很复杂）的编程代码，它们接收输入，进行转换或执行操作，然后返回结果。我们可以创建自己的函数，即使是简单的自定义函数，也能在数据分析时避免重复编写代码。

1.1 创建 percent_change() 函数

为了学习创建函数的语法，我们来编写一个函数，简化两个值的百分比变化计算，这在数据分析中是常见的操作。百分比变化公式为：
[
\text{百分比变化} = \frac{\text{新数值} - \text{旧数值}}{\text{旧数值}}
]

我们创建一个名为 percent_change() 的函数，它接受新数值和旧数值作为输入，并将结果四舍五入到用户指定的小数位数。以下是创建该函数的代码：

CREATE OR REPLACE FUNCTION
percent_change(new_value numeric,
               old_value numeric,
               decimal_places integer DEFAULT 1)
RETURNS numeric AS
'SELECT round(
          ((new_value - old_value) / old_value) * 100, decimal_places
  );'
LANGUAGE SQL
IMMUTABLE
RETURNS NULL ON NULL INPUT;

代码解释如下：
- CREATE OR REPLACE FUNCTION ：用于创建或替换一个函数。
- percent_change(new_value numeric, old_value numeric, decimal_places integer DEFAULT 1) ：函数名和参数列表， new_value 和 old_value 为 numeric 类型， decimal_places 为 integer 类型，默认值为 1。
- RETURNS numeric AS ：指定函数返回的结果类型为 numeric 。
- 'SELECT round( ((new_value - old_value) / old_value) * 100, decimal_places );' ：函数的主体，是一个 SELECT 查询，包含百分比变化计算并使用 round() 函数进行四舍五入。
- LANGUAGE SQL ：指定函数使用的语言为 SQL。
- IMMUTABLE ：表示函数不会对数据库进行任何更改，有助于提高性能。
- RETURNS NULL ON NULL INPUT ：确保如果任何非默认输入为 NULL ，函数将返回 NULL 。

使用 pgAdmin 运行上述代码，服务器将返回 CREATE FUNCTION 消息。

1.2 使用 percent_change() 函数

我们可以使用 SELECT 语句单独运行 percent_change() 函数进行测试：

SELECT percent_change(110, 108, 2);

运行结果如下：

percent_change
--------------
          1.85

这表明 108 到 110 之间有 1.85%的增长。我们还可以尝试使用其他数字，或者更改 decimal_places 参数的值（包括省略该参数），观察结果的变化。

接下来，我们使用 percent_change() 函数计算十年一次的人口普查数据中的人口百分比变化：

SELECT c2010.geo_name,
       c2010.state_us_abbreviation AS st,
       c2010.p0010001 AS pop_2010,
       percent_change(c2010.p0010001, c2000.p0010001) AS pct_chg_func,
       round( (CAST(c2010.p0010001 AS numeric(8,1)) - c2000.p0010001)
             / c2000.p0010001 * 100, 1 ) AS pct_chg_formula
FROM us_counties_2010 c2010 INNER JOIN us_counties_2000 c2000
ON c2010.state_fips = c2000.state_fips
   AND c2010.county_fips = c2000.county_fips
ORDER BY pct_chg_func DESC
LIMIT 5;

运行该查询后，结果将显示人口百分比变化最大的五个县，函数计算的结果应与直接在查询中输入公式计算的结果相匹配。

1.3 使用函数更新数据

我们还可以使用函数简化数据的常规更新操作。例如，根据教师的入职日期为其分配正确的个人天数（除假期外）。假设我们有一个 teachers 表，首先添加一个 personal_days 列：

ALTER TABLE teachers ADD COLUMN personal_days integer;
SELECT first_name,
       last_name,
       hire_date,
       personal_days
FROM teachers;

添加列后， personal_days 列的值为 NULL 。接下来，我们创建一个名为 update_personal_days() 的函数，根据以下规则更新 personal_days 列的值：
- 入职不足 5 年：3 天个人天数。
- 入职 5 到 10 年：4 天个人天数。
- 入职超过 10 年：5 天个人天数。

CREATE OR REPLACE FUNCTION update_personal_days()
RETURNS void AS $$
BEGIN
    UPDATE teachers
    SET personal_days = 
        CASE WHEN (now() - hire_date) BETWEEN '5 years'::interval
                                        AND '10 years'::interval THEN 4
             WHEN (now() - hire_date) > '10 years'::interval THEN 5
             ELSE 3
        END;
    RAISE NOTICE 'personal_days updated!';
END;
$$ LANGUAGE plpgsql;

代码解释如下：
- CREATE OR REPLACE FUNCTION update_personal_days() ：创建或替换函数。
- RETURNS void AS ：表示函数不返回数据，仅更新 personal_days 列。
- $$ ：使用非 ANSI SQL 标准的美元引号，标记函数命令字符串的开始和结束。
- BEGIN ... END; ：PL/pgSQL 函数的主体，包含一个 UPDATE 语句和一个 CASE 语句，用于根据入职日期更新 personal_days 列的值。
- RAISE NOTICE 'personal_days updated!'; ：在 pgAdmin 中显示函数执行完成的消息。
- LANGUAGE plpgsql ：指定函数使用的语言为 PL/pgSQL。

运行上述代码创建函数，然后使用以下语句在 pgAdmin 中运行该函数：

SELECT update_personal_days();

再次运行 SELECT 语句查看 teachers 表的数据， personal_days 列应已填充了相应的值。

1.4 在函数中使用 Python 语言

PostgreSQL 默认的过程语言是 PL/pgSQL，但它也支持使用开源语言（如 Perl 和 Python）创建函数。为了在函数中使用 Python，我们需要添加 plpythonu 扩展：

CREATE EXTENSION plpythonu;

注意， plpythonu 目前安装的是 Python 2.x 版本。如果要使用 Python 3.x，应安装 plpython3u 扩展。

添加扩展后，我们创建一个名为 trim_county() 的函数，用于从字符串末尾删除“County”一词：

CREATE OR REPLACE FUNCTION trim_county(input_string text)
RETURNS text AS $$
    import re
    cleaned = re.sub(r' County', '', input_string)
    return cleaned
$$ LANGUAGE plpythonu;

代码解释如下：
- CREATE OR REPLACE FUNCTION trim_county(input_string text) ：创建或替换函数，接受一个 text 类型的输入。
- RETURNS text AS ：指定函数返回的结果类型为 text 。
- import re ：导入 Python 的正则表达式模块。
- cleaned = re.sub(r' County', '', input_string) ：使用正则表达式替换函数 re.sub() ，将输入字符串中的“ County”替换为空字符串。
- return cleaned ：返回处理后的字符串。
- LANGUAGE plpythonu ：指定函数使用的语言为 PL/Python。

运行代码创建函数，然后使用以下 SELECT 语句测试该函数：

SELECT geo_name,
       trim_county(geo_name)
FROM us_counties_2010
ORDER BY state_fips, county_fips
LIMIT 5;

运行结果如下：
| geo_name | trim_county |
| — | — |
| Autauga County | Autauga |
| Baldwin County | Baldwin |
| Barbour County | Barbour |
| Bibb County | Bibb |
| Blount County | Blount |

可以看到， trim_county() 函数对 geo_name 列中的每个值进行了处理，删除了“ County”。

2. 使用触发器自动化数据库操作

数据库触发器在指定事件（如 INSERT 、 UPDATE 或 DELETE ）发生在表或视图上时执行一个函数。我们可以设置触发器在事件之前、之后或替代事件触发，还可以设置触发器为受事件影响的每一行执行一次，或为每个操作执行一次。

2.1 记录成绩更新到表

假设我们要自动跟踪学校数据库中学生成绩表的更改。每次更新一行时，我们希望记录旧成绩和新成绩以及更改发生的时间。为了自动处理这个任务，我们需要以下三个部分：
- 一个 grades_history 表，用于记录 grades 表中成绩的更改。
- 一个触发器 grades_update ，在 grades 表发生更改时运行一个函数。
- 触发器将执行的函数 record_if_grade_changed() 。

2.1.1 创建用于跟踪成绩和更新的表

首先，我们创建 grades 表并插入一些数据，然后创建 grades_history 表：

CREATE TABLE grades (
    student_id bigint,
    course_id bigint,
    course varchar(30) NOT NULL,
    grade varchar(5) NOT NULL,
    PRIMARY KEY (student_id, course_id)
);
INSERT INTO grades
VALUES
    (1, 1, 'Biology 2', 'F'),
    (1, 2, 'English 11B', 'D'),
    (1, 3, 'World History 11B', 'C'),
    (1, 4, 'Trig 2', 'B');
CREATE TABLE grades_history (
    student_id bigint NOT NULL,
    course_id bigint NOT NULL,
    change_time timestamp with time zone NOT NULL,
    course varchar(30) NOT NULL,
    old_grade varchar(5) NOT NULL,
    new_grade varchar(5) NOT NULL,
    PRIMARY KEY (student_id, course_id, change_time)
);

2.1.2 创建函数和触发器

接下来，我们编写 record_if_grade_changed() 函数：

CREATE OR REPLACE FUNCTION record_if_grade_changed()
RETURNS trigger AS
$$
BEGIN
    IF NEW.grade <> OLD.grade THEN
        INSERT INTO grades_history (
            student_id,
            course_id,
            change_time,
            course,
            old_grade,
            new_grade)
        VALUES
            (OLD.student_id,
             OLD.course_id,
             now(),
             OLD.course,
             OLD.grade,
             NEW.grade);
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

代码解释如下：
- CREATE OR REPLACE FUNCTION record_if_grade_changed() ：创建或替换函数。
- RETURNS trigger AS ：指定函数返回的结果类型为 trigger 。
- IF NEW.grade <> OLD.grade THEN ... END IF; ：使用 IF ... THEN 语句，仅当更新后的成绩与旧成绩不同时，才将记录插入 grades_history 表。
- INSERT INTO grades_history ... VALUES ... ：插入记录到 grades_history 表。
- RETURN NEW; ：触发器必须有一个 RETURN 语句。

运行上述代码创建函数，然后添加 grades_update 触发器到 grades 表：

CREATE TRIGGER grades_update
AFTER UPDATE
ON grades
FOR EACH ROW
EXECUTE PROCEDURE record_if_grade_changed();

代码解释如下：
- CREATE TRIGGER grades_update ：创建触发器。
- AFTER UPDATE ：指定触发器在 grades 表更新后触发。
- FOR EACH ROW ：表示触发器为每一行更新执行一次。
- EXECUTE PROCEDURE record_if_grade_changed() ：指定触发器要执行的函数。

运行上述代码创建触发器，数据库将返回 CREATE TRIGGER 消息。

2.1.3 测试触发器

首先，运行 SELECT * FROM grades_history; ，会发现表为空，因为还没有对 grades 表进行任何更改。然后，运行 SELECT * FROM grades; ，可以看到成绩数据。我们更新 Biology 2 的成绩：

UPDATE grades
SET grade = 'C'
WHERE student_id = 1 AND course_id = 1;

运行 UPDATE 语句后， pgAdmin 仅报告 UPDATE 1 ，但触发器实际上已经运行。我们可以通过以下 SELECT 查询查看 grades_history 表的内容：

SELECT student_id,
       change_time,
       course,
       old_grade,
       new_grade
FROM grades_history;

运行结果将显示旧成绩、新成绩和更改时间。

2.2 自动分类温度

我们可以使用触发器自动对温度读数进行分类。首先，创建一个 temperature_test 表：

CREATE TABLE temperature_test (
    station_name varchar(50),
    observation_date date,
    max_temp integer,
    min_temp integer,
    max_temp_group varchar(40),
    PRIMARY KEY (station_name, observation_date)
);

然后，创建一个名为 classify_max_temp() 的函数，根据最高温度对其进行分类：

CREATE OR REPLACE FUNCTION classify_max_temp()
RETURNS trigger AS
$$
BEGIN
    CASE 
        WHEN NEW.max_temp >= 90 THEN
            NEW.max_temp_group := 'Hot';
        WHEN NEW.max_temp BETWEEN 70 AND 89 THEN
            NEW.max_temp_group := 'Warm';
        WHEN NEW.max_temp BETWEEN 50 AND 69 THEN
            NEW.max_temp_group := 'Pleasant';
        WHEN NEW.max_temp BETWEEN 33 AND 49 THEN
            NEW.max_temp_group :=  'Cold';
        WHEN NEW.max_temp BETWEEN 20 AND 32 THEN
            NEW.max_temp_group :=  'Freezing';
        ELSE NEW.max_temp_group :=  'Inhumane';
    END CASE;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

代码解释如下：
- CREATE OR REPLACE FUNCTION classify_max_temp() ：创建或替换函数。
- RETURNS trigger AS ：指定函数返回的结果类型为 trigger 。
- CASE ... END CASE; ：使用 PL/pgSQL 版本的 CASE 语句，根据最高温度为 NEW.max_temp_group 列赋值。
- RETURN NEW; ：返回处理后的行。

运行上述代码创建函数，然后创建一个触发器 temperature_insert ，在插入行之前执行该函数：

CREATE TRIGGER temperature_insert
BEFORE INSERT
ON temperature_test
FOR EACH ROW
EXECUTE PROCEDURE classify_max_temp();

代码解释如下：
- CREATE TRIGGER temperature_insert ：创建触发器。
- BEFORE INSERT ：指定触发器在插入行之前触发。
- FOR EACH ROW ：表示触发器为每一行插入执行一次。
- EXECUTE PROCEDURE classify_max_temp() ：指定触发器要执行的函数。

运行上述代码创建触发器，然后插入一些数据进行测试：

INSERT INTO temperature_test (station_name, observation_date, max_temp, min_temp)
VALUES
    ('North Station', '1/19/2019', 10, -3),
    ('North Station', '3/20/2019', 28, 19),
    ('North Station', '5/2/2019', 65, 42),
    ('North Station', '8/9/2019', 93, 74);
SELECT * FROM temperature_test;

运行结果将显示插入的数据以及分类后的 max_temp_group 列的值。

综上所述，自定义函数和触发器可以帮助我们简化数据处理和管理任务，提高工作效率。通过合理使用这些功能，我们可以更好地控制数据库中的数据。

3. 自定义函数与触发器的优势总结

3.1 自定义函数的优势

自定义函数在数据处理和分析中具有显著优势，主要体现在以下几个方面：
- 代码复用 ：避免了重复编写相同的代码，例如 percent_change() 函数，在需要计算百分比变化时，只需调用该函数，而无需每次都编写复杂的计算公式。
- 提高效率 ：对于复杂的计算或操作，封装在函数中可以减少代码的冗余，提高执行效率。例如 update_personal_days() 函数，将根据教师入职日期分配个人天数的逻辑封装起来，使得代码更简洁，易于维护。
- 增强可读性 ：自定义函数可以使用有意义的名称，使代码更易于理解。例如 trim_county() 函数，从名称上就可以清楚地知道它的作用是去除字符串末尾的“County”。

3.2 触发器的优势

触发器在数据库操作自动化方面发挥着重要作用，其优势如下：
- 自动记录 ：如 grades_update 触发器，能够自动记录成绩表的更改历史，无需手动干预，保证了数据的完整性和可追溯性。
- 实时处理 ：在数据插入、更新或删除时，触发器可以立即执行相应的操作，例如 temperature_insert 触发器，在插入温度数据时，自动对最高温度进行分类，提高了数据处理的实时性。
- 数据一致性 ：通过触发器可以确保数据符合特定的规则和约束，例如在记录成绩更改时，只有当成绩发生变化时才会记录到历史表中，保证了数据的一致性。

3.3 两者结合的优势

将自定义函数和触发器结合使用，可以进一步提升数据库的功能和性能。例如，触发器可以调用自定义函数来完成复杂的任务，实现更高级的自动化操作。

4. 注意事项与最佳实践

4.1 自定义函数的注意事项

• 数据类型匹配 ：在定义函数时，要确保输入参数和返回值的数据类型匹配，否则可能会导致数据丢失或错误。例如，在 percent_change() 函数中，输入参数 new_value 和 old_value 都定义为 numeric 类型，以确保精确的计算。
• 性能优化 ：对于复杂的函数，要注意性能优化。可以使用 IMMUTABLE 关键字来表明函数不会对数据库进行更改，提高函数的执行效率。
• 错误处理 ：在函数中要考虑错误处理，例如在 update_personal_days() 函数中，使用 RAISE NOTICE 来显示函数执行完成的消息，方便调试和监控。

4.2 触发器的注意事项

• 触发时机选择 ：根据实际需求选择合适的触发时机，如 BEFORE 、 AFTER 或 INSTEAD OF 。例如， temperature_insert 触发器选择在插入行之前触发，以提高数据处理的效率。
• 避免无限循环 ：在触发器中要避免出现无限循环的情况，例如触发器触发的函数又会导致触发器再次触发。
• 性能影响 ：触发器的执行可能会对数据库性能产生一定的影响，因此要谨慎使用，避免在高并发场景下使用过于复杂的触发器。

4.3 最佳实践

• 模块化设计 ：将功能拆分成多个小的函数和触发器，提高代码的可维护性和复用性。
• 测试与验证 ：在实际应用之前，要对函数和触发器进行充分的测试和验证，确保其功能正确。
• 文档记录 ：对函数和触发器的功能、输入参数、返回值等进行详细的文档记录，方便后续的开发和维护。

5. 总结与展望

自定义函数和触发器是数据库中强大的工具，它们可以帮助我们简化数据处理和管理任务，提高工作效率。通过合理使用自定义函数和触发器，我们可以实现更高级的自动化操作，保证数据的一致性和完整性。

在未来的数据库开发中，随着数据量的不断增加和业务需求的不断变化，自定义函数和触发器的应用将会更加广泛。我们可以进一步探索如何将它们与其他技术（如机器学习、大数据分析等）相结合，为数据处理和分析带来更多的可能性。

同时，我们也要不断关注数据库技术的发展，学习新的函数和触发器的使用方法，以适应不断变化的业务需求。例如，随着 PostgreSQL 对更多编程语言的支持，我们可以尝试使用更多的语言来创建函数，发挥不同语言的优势。

总之，自定义函数和触发器为我们提供了一个强大的工具集，我们应该充分利用它们，为数据处理和管理带来更多的便利和价值。

5.1 流程总结

下面是一个 mermaid 格式的流程图，总结了自定义函数和触发器的创建和使用流程：

graph LR
    classDef startend fill:#F5EBFF,stroke:#BE8FED,stroke-width:2px
    classDef process fill:#E5F6FF,stroke:#73A6FF,stroke-width:2px
    classDef decision fill:#FFF6CC,stroke:#FFBC52,stroke-width:2px

    A([开始]):::startend --> B(创建自定义函数):::process
    B --> C{是否需要触发器}:::decision
    C -->|是| D(创建触发器):::process
    C -->|否| E(使用自定义函数):::process
    D --> E
    E --> F(测试和验证):::process
    F --> G(投入使用):::process
    G --> H([结束]):::startend

5.2 关键知识点总结

知识点	描述	示例代码
自定义函数	封装常用的计算或操作，提高代码复用性和可读性	CREATE OR REPLACE FUNCTION percent_change(new_value numeric, old_value numeric, decimal_places integer DEFAULT 1) RETURNS numeric AS ...
触发器	在指定事件发生时自动执行函数，实现数据库操作自动化	CREATE TRIGGER grades_update AFTER UPDATE ON grades FOR EACH ROW EXECUTE PROCEDURE record_if_grade_changed();
PL/pgSQL	PostgreSQL 的过程语言，支持逻辑控制结构和变量赋值	CREATE OR REPLACE FUNCTION update_personal_days() RETURNS void AS $$ BEGIN ... END; $$ LANGUAGE plpgsql;
Python 扩展	支持在函数中使用 Python 语言，发挥 Python 的强大功能	CREATE EXTENSION plpythonu; CREATE OR REPLACE FUNCTION trim_county(input_string text) RETURNS text AS $$ import re ... return cleaned $$ LANGUAGE plpythonu;