PostgreSQL查询无结果时CASE表达式为何未返回预期值？

你遇到的不是CASE表达式的问题，而是PostgreSQL和SQL Server在SELECT赋值逻辑上的行为差异：

核心差异点 #

• SQL Server：当SELECT语句没有返回任何行时，用来接收值的变量会保留原有值，不会被修改。所以你的SQL Server代码里，@serviceId初始设为12，SELECT无返回行，最终结果还是12。
• PostgreSQL：SELECT ... INTO语句如果查询返回0行，会直接把目标变量设置为NULL，不管变量之前的取值是什么。这就是你看到_serviceId变成NULL的原因——因为没有匹配的行，CASE表达式根本没被执行，变量直接被置空了。

你可以做个简单验证：把CASE换成固定值，比如select 12 into _serviceId from rep.reportparameters where 1=2，结果依然会把_serviceId设为NULL，这就说明和CASE无关，是SELECT INTO的行为导致的。

解决办法 #

方法1：用UNION ALL添加默认行 #

通过UNION ALL追加一行原有变量值，保证查询至少返回一行，再用limit 1取结果：

do $$
declare _reportId bigint := null;
        _serviceId bigint := null; 
begin
    _serviceId := 12;
    _reportId := null;

    select  case when _serviceId is null then rp.value::bigint else _serviceId end
    into    _serviceId
    from    rep.reportparameters as rp
    where   rp.reportid = _reportId
    union all
    select _serviceId  -- 无匹配行时返回原有值
    limit 1;

    RAISE NOTICE '%', _serviceId;
end;
$$; 

方法2：捕获NO_DATA_FOUND异常 #

利用PL/pgSQL的异常处理，捕获查询无返回行的情况，此时不修改变量：

do $$
declare _reportId bigint := null;
        _serviceId bigint := null; 
begin
    _serviceId := 12;
    _reportId := null;

    begin
        select  case when _serviceId is null then rp.value::bigint else _serviceId end
        into    _serviceId
        from    rep.reportparameters as rp
        where   rp.reportid = _reportId;
    exception
        when NO_DATA_FOUND then
            -- 无返回行时不做操作，保留原有值
            null;
    end;

    RAISE NOTICE '%', _serviceId;
end;
$$; 

方法3：用COALESCE包裹子查询 #

将查询作为子查询，利用子查询无返回时返回NULL的特性，再用COALESCE和原有值合并：

do $$
declare _reportId bigint := null;
        _serviceId bigint := null; 
begin
    _serviceId := 12;
    _reportId := null;

    _serviceId := COALESCE(
        (select case when _serviceId is null then rp.value::bigint else _serviceId end
         from rep.reportparameters as rp
         where rp.reportid = _reportId),
        _serviceId
    );

    RAISE NOTICE '%', _serviceId;
end;
$$; 

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