PostgreSQL / 系统课时
创建 SQL 函数
创建 SQL 函数
本课解决相同计算逻辑散落在多条 SQL 中的问题。你将创建一个可直接调用的 SQL 函数,理解参数、返回类型和函数属性,并学会在修改定义后验证结果。
学习目标
- 使用
CREATE FUNCTION创建 SQL 函数 - 正确声明参数、返回类型和函数属性
- 调用、替换和删除函数
SQL 函数的基本结构
SQL 函数由函数名、参数、返回类型、语言和函数体组成。它适合封装单条查询或简单计算;需要变量、条件分支、循环和异常处理时,通常改用 PL/pgSQL。
函数签名由名称和参数类型共同决定,因此同名函数可以根据参数类型重载。删除函数时应写出参数类型,避免误删或找不到目标函数。
创建并调用函数
下面的函数根据数量、单价和折扣率计算订单金额。先运行 psql -d <DATABASE>,再执行完整脚本。
sql
DROP FUNCTION IF EXISTS calculate_order_total(integer, numeric, numeric);
CREATE FUNCTION calculate_order_total(
p_quantity integer,
p_unit_price numeric,
p_discount_rate numeric DEFAULT 0
)
RETURNS numeric
LANGUAGE sql
IMMUTABLE
STRICT
AS $$
SELECT round(
p_quantity * p_unit_price * (1 - p_discount_rate),
2
);
$$;
SELECT calculate_order_total(3, 20.00, 0.10) AS discounted_total;
SELECT calculate_order_total(2, 15.50) AS regular_total;预期结果
text
discounted_total
------------------
54.00
(1 row)
regular_total
---------------
31.00
(1 row)DEFAULT 0 允许省略折扣率。STRICT 表示任一参数为 NULL 时直接返回 NULL,无需执行函数体。IMMUTABLE 表示相同输入始终得到相同输出,便于优化器安全地进行表达式优化。
验证空值与参数边界
继续执行以下查询,验证 STRICT 行为。
sql
SELECT calculate_order_total(NULL, 20.00, 0.10) AS null_result;预期结果
text
null_result
-------------
(1 row)空白值表示 SQL NULL。本函数没有限制负数量或大于 1 的折扣率,生产使用时应在表约束、调用前校验或函数体中补充规则,避免得到不合理金额。
修改与删除函数
当参数和返回类型不变时,可以使用 CREATE OR REPLACE FUNCTION 更新函数体。若要改变函数签名,通常需要删除旧函数再创建新函数,并检查依赖它的视图、索引或其他函数。
删除本例函数使用 DROP FUNCTION calculate_order_total(integer, numeric, numeric);。生产环境执行前应确认没有业务 SQL 仍在调用它。
按自己的节奏,确认真正理解。