如何重构SQL查询以适配返回多条记录的函数

问题背景 #

原my_func函数通过LIMIT 1返回单条记录，可配合主查询的JOIN与WHERE子句正常运行。需求变更后需移除LIMIT，让函数返回多条记录。直接将主查询中的=替换为IN虽能运行，但会重复调用函数引发性能问题；尝试用FOR LOOP重构时触发错误：ERROR: cannot use RETURN QUERY in a non-SETOF function。

原函数与查询 #

CREATE FUNCTION my_func(pId bigint) RETURNS TABLE (table2_id bigint, column_1 bigint, field text) AS $$
BEGIN
  RETURN QUERY SELECT table2_id, table3_id, column_1, field 
    FROM t_name
  LEFT JOIN t2_name on t_name.key = t2_name.key
  WHERE t2_name.pId = pId
    ORDER BY table2_id DESC
    LIMIT 1;
END;$$ LANGUAGE plpgsql;

SELECT column_1, column_2
FROM table_name 
LEFT JOIN table2_name ON table2_name.table2_id = (SELECT rec.table2_id FROM my_func(table_name.column_1))
WHERE column_1 = (SELECT rec.column_1 FROM my_func(table_name.column_1))
AND table2_name.field = (SELECT rec.field FROM my_func(table_name.column_1))
GROUP BY column_1, column_2
ORDER BY column_1 ASC, column_2;

尝试的低效修改写法 #

LEFT JOIN table2_name ON table2_name.table2_id IN (SELECT rec.table2_id FROM my_func(table_name.column_1))
WHERE column_1 IN (SELECT rec.column_1 FROM my_func(table_name.column_1))
AND table2_name.field IN (SELECT rec.field FROM my_func(table_name.column_1))

报错的FOR LOOP尝试代码 #

DO
$$
DECLARE
  rec record;
BEGIN
FOR rec IN (SELECT table2_id, table3_id, column_1, field 
    FROM t_name
  LEFT JOIN t2_name on t_name.key = t2_name.key
  ORDER BY table2_id DESC) 
LOOP
  RETURN QUERY EXECUTE 
      SELECT column_1, column_2
      FROM table_name 
      LEFT JOIN table2_name ON table2_name.table2_id = rec.table2_id
      WHERE column_1 = rec.column_1
      AND table2_name.field = rec.field
      GROUP BY column_1, column_2
      ORDER BY column_1 ASC, column_2;
END LOOP
END;
$$

重构方案 #

第一步：修正函数定义 #

先确保函数返回多条记录（移除LIMIT 1），同时修正原函数中SELECT字段与返回表定义不匹配的问题（原SELECT包含table3_id但返回表未定义该字段）：

CREATE OR REPLACE FUNCTION my_func(pId bigint) RETURNS TABLE (table2_id bigint, column_1 bigint, field text) AS $$
BEGIN
  RETURN QUERY SELECT table2_id, column_1, field 
    FROM t_name
    LEFT JOIN t2_name ON t_name.key = t2_name.key
    WHERE t2_name.pId = pId
    ORDER BY table2_id DESC;
END;$$ LANGUAGE plpgsql;

第二步：用LATERAL JOIN重构主查询（推荐） #

核心是通过LATERAL JOIN一次性获取函数返回的所有记录，避免重复调用函数，同时完成关联与过滤：

SELECT tn.column_1, tn.column_2
FROM table_name tn
LEFT JOIN LATERAL my_func(tn.column_1) mf ON true
LEFT JOIN table2_name t2n 
  ON t2n.table2_id = mf.table2_id 
  AND t2n.field = mf.field
WHERE tn.column_1 = mf.column_1  -- 若需过滤匹配记录则保留，否则可移除该条件
GROUP BY tn.column_1, tn.column_2
ORDER BY tn.column_1 ASC, tn.column_2;

• LATERAL JOIN会为table_name的每一行调用一次my_func，将返回的所有记录与主行关联，彻底消除重复调用的性能损耗。
• 直接使用函数返回字段进行关联和过滤，逻辑更清晰，执行效率更高。

第三步：修正FOR LOOP写法（备选） #

之前的DO块是匿名代码块，不支持返回结果集，RETURN QUERY仅能用于返回SETOF类型的函数。若要保留循环逻辑，需将代码封装为返回表的函数：

CREATE OR REPLACE FUNCTION get_final_result() RETURNS TABLE (column_1 bigint, column_2 bigint) AS $$
DECLARE
  rec record;
BEGIN
  FOR rec IN (
    SELECT table2_id, column_1, field 
    FROM t_name
    LEFT JOIN t2_name ON t_name.key = t2_name.key
    ORDER BY table2_id DESC
  ) LOOP
    RETURN QUERY 
      SELECT tn.column_1, tn.column_2
      FROM table_name tn
      LEFT JOIN table2_name t2n ON t2n.table2_id = rec.table2_id
      WHERE tn.column_1 = rec.column_1
        AND t2n.field = rec.field
      GROUP BY tn.column_1, tn.column_2
      ORDER BY tn.column_1 ASC, tn.column_2;
  END LOOP;
END;$$ LANGUAGE plpgsql;

注意：这种循环写法性能通常不如LATERAL JOIN的集合式查询，仅作为备选方案。

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