PostgreSQL函数基于用户角色的CASE条件查询失效求助

原本运行正常的PostgreSQL函数，新增用户类型后添加CASE条件逻辑未达预期效果。需求明确：

• 若用户角色为ALPHA，需应用最后两个WHERE子句
• 若为BETA，仅应用倒数第二个WHERE子句，忽略最后一个

旧版无角色判断代码 #

begin
    return query SELECT distinct(gl.user_id) as user_id, u.name_tx FROM contact_linking cl
    INNER JOIN group_contacts gc ON gc.contact_id = cl.contact_id
    INNER JOIN group_linking gl ON gl.group_id = gc.group_id
    INNER JOIN group_contacts_w gcw ON gcw.group_link_id = gl.group_link_id
    INNER JOIN users u ON u.user_id = gl.user_id
    WHERE cl.ref_contact_type_cd = 'PRIMARY' 
    AND cl.users_id = userId AND cl.activ_yn = 'Y' AND gl.activ_yn = 'Y' AND cl.contact_id IS NOT NULL
    AND gc.type LIKE 'ALPHA%'
    AND gcw.type = gc.type

    UNION ALL
    select userId as user_id; 
end

修改后未达预期的代码 #

begin
    return query SELECT distinct(gl.user_id) as user_id FROM contact_linking cl
    INNER JOIN group_contacts gc ON gc.contact_id = cl.contact_id
    INNER JOIN group_linking gl ON gl.group_id = gc.group_id
    INNER JOIN group_contacts_w gcw ON gcw.group_link_id = gl.group_link_id
    INNER JOIN users u ON u.user_id = gl.user_id
    WHERE cl.ref_contact_type_cd = 'PRIMARY' 
    AND cl.users_id = userId AND cl.activ_yn = 'Y' AND gl.activ_yn = 'Y' AND cl.contact_id IS NOT NULL
    AND CASE 
        WHEN 'ALPHA' = (SELECT ref_user_cd FROM users WHERE user_id = userId) THEN gc.type LIKE 'ALPHA%'
        WHEN 'BETA' = (SELECT ref_user_cd FROM users WHERE user_id = userId) THEN gc.type LIKE '%BETA'
        ELSE true
    END
    AND CASE 
        WHEN 'ALPHA' = (SELECT ref_user_cd FROM users WHERE user_id = userId) THEN gcw.type = gc.type
        ELSE true
    END
        
    UNION ALL
    select userId as user_id; 
end

修复方案 #

你的问题核心是CASE表达式写法不够直观，且重复查询用户角色既影响性能又容易出错。直接用逻辑表达式组合替代CASE，同时提前获取用户角色，修复后的代码如下：

begin
    -- 提前获取用户角色，避免重复查询users表
    DECLARE user_role TEXT := (SELECT ref_user_cd FROM users WHERE user_id = userId);
    
    return query SELECT distinct(gl.user_id) as user_id, u.name_tx 
    FROM contact_linking cl
    INNER JOIN group_contacts gc ON gc.contact_id = cl.contact_id
    INNER JOIN group_linking gl ON gl.group_id = gc.group_id
    INNER JOIN group_contacts_w gcw ON gcw.group_link_id = gl.group_link_id
    INNER JOIN users u ON u.user_id = gl.user_id
    WHERE cl.ref_contact_type_cd = 'PRIMARY' 
      AND cl.users_id = userId 
      AND cl.activ_yn = 'Y' 
      AND gl.activ_yn = 'Y' 
      AND cl.contact_id IS NOT NULL
      -- 按角色匹配gc.type规则
      AND (
          (user_role = 'ALPHA' AND gc.type LIKE 'ALPHA%')
          OR (user_role = 'BETA' AND gc.type LIKE '%BETA')
          OR user_role NOT IN ('ALPHA', 'BETA')
      )
      -- 仅ALPHA角色需满足gcw.type与gc.type相等，其他角色跳过该条件
      AND (user_role != 'ALPHA' OR gcw.type = gc.type)
            
    UNION ALL
    select userId as user_id; 
end

优化说明 #

• 新增user_role变量，只查询一次用户角色，提升执行效率
• 用逻辑OR/AND替代CASE表达式，逻辑更清晰，数据库能更好地优化执行计划
• 恢复了修改代码中被误删的u.name_tx字段，保证返回结果和旧版一致
• 明确处理非ALPHA/BETA的角色场景，保持原有兼容性

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