PostgreSQL 12 函数中如何按参数选择JOIN类型且避免重复写SELECT语句

核心思路 #

JOIN类型属于SQL语法结构，无法直接用CASE等运行时表达式动态切换，我们可以用「LEFT JOIN + 动态过滤条件」的方式实现需求：LEFT JOIN本身会保留A表的所有行，当需要INNER JOIN效果时，只需要过滤掉B表关联字段为空的行即可，全程只需要写一条SELECT语句。

实现代码 #

基础查询逻辑 #

-- 入参 p_is_inner boolean 为true时返回INNER JOIN结果，false返回LEFT JOIN结果
SELECT * 
FROM A
LEFT JOIN B ON A.id = B.id
WHERE (p_is_inner = FALSE OR B.id IS NOT NULL);

完整函数示例 #

CREATE OR REPLACE FUNCTION get_ab_join_result(p_is_inner boolean)
RETURNS TABLE (
  -- 这里按实际业务需求定义返回的列，也可以用SETOF record配合调用时指定结构
  a_id INT,
  a_col1 TEXT,
  b_id INT,
  b_col1 TEXT
) 
LANGUAGE sql STABLE
AS $$
SELECT A.id, A.col1, B.id, B.col1
FROM A
LEFT JOIN B ON A.id = B.id
WHERE (p_is_inner = FALSE OR B.id IS NOT NULL);
$$;

原理解释 #

WHERE条件的判断逻辑：

• 当入参p_is_inner为false时，(p_is_inner = FALSE)条件成立，整段WHERE条件为真，不会做额外过滤，返回LEFT JOIN的全部结果
• 当入参p_is_inner为true时，p_is_inner = FALSE不成立，必须满足B.id IS NOT NULL才会返回行，刚好等价于INNER JOIN的结果
  该方案不会有性能损失，PostgreSQL查询优化器会自动根据入参常量优化执行计划，和直接写两种JOIN的执行效率完全一致。

注意事项 #

• 如果B表的关联字段允许为NULL，且业务需要保留关联上但B关联字段为NULL的行，可以把过滤条件改为(p_is_inner = FALSE OR B.id IS NOT NULL OR A.id = B.id)，绝大多数场景下关联主键都是非空的，不需要额外调整
• 不推荐使用SELECT *返回结果，避免A、B表出现同名字段时返回结构冲突，建议明确列出需要返回的字段

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