如何在CASE中嵌套SELECT？PostgreSQL函数实现交付状态判断

需求 #

基于Delivery表的status字段判断对应对象是否可投入使用：状态为Delivered时标记为Ready（可用），状态为In Transit或is going时标记为Not ready（不可用），需将该判断逻辑封装为PostgreSQL函数。

用户尝试的代码 #

初始CASE语句尝试 #

DO
$$
BEGIN 
CASE
WHEN status IN ('In Transit','is going') THEN 'Not ready';
ELSE 'Ready';
end case;
end $$;

IF语句版本尝试 #

CREATE OR REPLACE FUNCTION prepare_object
()
RETURNS SETOF delivery
LANGUAGE 'plpgsql'
AS
$$
DECLARE answer varchar;
DECLARE status varchar;
    BEGIN
        SELECT * FROM delivery
            if status IN ('In Transit','is going');
                THEN answer = ('Not ready');
                ELSE answer = ('Ready');
            END IF;
    END;
    RETURN;
$$

Delivery表结构 #

CREATE TABLE Delivery
( 
Delivery_Code Serial PRIMARY KEY UNIQUE,
Status varchar(255) CHECK (Status = 'Delivered' OR Status = 'In Transit' 
OR Status = 'is going'),
Composition_Delivery varchar(255),
Date_and_time timestamp,
Price money, 
Supplier_code Serial REFERENCES Provider(Supplier_code),
Request_Code Serial REFERENCES Request(Request_Code),
Object_ID serial REFERENCES Object(Object_ID)
);

问题分析 #

用户的两次尝试均存在问题：

• 初始DO块未关联Delivery表的实际数据，无法获取每条记录的status值进行判断；
• IF语句版本的函数存在语法错误（IF语句格式不符合PL/pgSQL规范），且未正确查询或遍历数据，返回类型与逻辑不匹配。

正确的函数实现 #

方案1：查询所有对象的可用性状态 #

如果需要批量获取所有对象的可用性，可创建返回结果集的函数：

CREATE OR REPLACE FUNCTION get_object_readiness()
RETURNS TABLE (object_id INT, readiness VARCHAR)
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN QUERY
        SELECT 
            d.Object_ID,
            CASE
                WHEN d.Status IN ('In Transit', 'is going') THEN 'Not ready'
                WHEN d.Status = 'Delivered' THEN 'Ready'
                ELSE 'Unknown' -- 虽有CHECK约束，保留兜底逻辑
            END AS readiness
        FROM Delivery d;
END;
$$;

方案2：根据指定对象ID查询可用性 #

如果需要针对单个对象查询，可创建带参数的函数：

CREATE OR REPLACE FUNCTION get_object_readiness(p_object_id INT)
RETURNS VARCHAR
LANGUAGE plpgsql
AS $$
DECLARE
    v_status VARCHAR;
BEGIN
    SELECT Status INTO v_status
    FROM Delivery
    WHERE Object_ID = p_object_id;

    IF NOT FOUND THEN
        RETURN 'Object not found';
    END IF;

    CASE
        WHEN v_status IN ('In Transit', 'is going') THEN RETURN 'Not ready';
        WHEN v_status = 'Delivered' THEN RETURN 'Ready';
        ELSE RETURN 'Unknown';
    END CASE;
END;
$$;

调用示例 #

• 调用方案1函数：SELECT * FROM get_object_readiness();
• 调用方案2函数：SELECT get_object_readiness(1);（将1替换为实际的Object_ID）

内容的提问来源于stack exchange，提问作者Hachaika