PostgreSQL：如何基于枚举列当前值实现条件更新存储过程？

数据库架构 #

CREATE TYPE letter_enum AS ENUM ('A', 'B', 'C', 'D');

CREATE TABLE foo(
  id int,
  letter letter_enum
);

需求 #

需要创建一个存储过程，满足以下要求：

• 接收id和letter两个参数
• 按照以下规则更新指定id的letter字段：
  • 当前值为A时，允许更新为B或C，禁止更新为D
  • 当前值为B时，允许更新为D，禁止更新为A或C
  • 当前值为C时，允许更新为D，禁止其他所有更新
  • 当前值为D时，禁止所有更新操作

问题背景 #

这些状态变更规则无固定模式，与枚举定义顺序无关。尝试过用MAX()/MIN()函数实现，但枚举类型的特性限制了这种方法；现有相关问题要么未覆盖枚举类型，要么依赖枚举定义顺序，无法满足需求。

解决方案 #

可以通过在存储过程中明确枚举值的合法转换规则，使用条件判断过滤合法操作，同时抛出异常处理非法转换。以下是具体实现：

CREATE OR REPLACE PROCEDURE update_foo_letter(p_id INT, p_new_letter letter_enum)
LANGUAGE plpgsql
AS $$
DECLARE
    v_current_letter letter_enum;
BEGIN
    -- 获取当前记录的letter值并加锁，防止并发更新
    SELECT letter INTO v_current_letter
    FROM foo
    WHERE id = p_id
    FOR UPDATE;

    -- 检查记录是否存在
    IF NOT FOUND THEN
        RAISE EXCEPTION '记录不存在，ID: %', p_id;
    END IF;

    -- 校验状态转换合法性
    CASE v_current_letter
        WHEN 'A' THEN
            IF p_new_letter NOT IN ('B', 'C') THEN
                RAISE EXCEPTION '当前值为A时，仅允许更新为B或C';
            END IF;
        WHEN 'B' THEN
            IF p_new_letter != 'D' THEN
                RAISE EXCEPTION '当前值为B时，仅允许更新为D';
            END IF;
        WHEN 'C' THEN
            IF p_new_letter != 'D' THEN
                RAISE EXCEPTION '当前值为C时，仅允许更新为D';
            END IF;
        WHEN 'D' THEN
            RAISE EXCEPTION '当前值为D时，禁止所有更新';
    END CASE;

    -- 执行合法更新
    UPDATE foo
    SET letter = p_new_letter
    WHERE id = p_id;
END;
$$;

代码说明 #

1. 并发安全：使用FOR UPDATE锁定目标记录，避免并发更新导致的状态不一致。
2. 存在性校验：如果指定ID的记录不存在，直接抛出异常提示。
3. 规则校验：通过CASE语句针对每个枚举值明确校验允许的目标值，非法转换直接抛出对应异常，逻辑清晰且不依赖枚举定义顺序。
4. 枚举值直接比较：直接使用枚举字面量判断，完全脱离枚举类型的定义顺序限制，适配任意自定义转换规则。

使用示例 #

-- 合法操作：将ID为1的记录从A更新为B
CALL update_foo_letter(1, 'B');

-- 非法操作：将ID为1的记录从B更新为A，会抛出异常
CALL update_foo_letter(1, 'A');

内容的提问来源于stack exchange，提问作者postgres-user-12