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 仍在调用它。

按自己的节奏,确认真正理解。

目录与阅读设置

阅读设置

17

记录一个想法

创建 SQL 函数

0 / 10000 · 笔记保存到账号。未提交的修改会在关闭、离开或刷新后丢失。

登录哪都通