PL/pgSQL函数返回SETOF自定义复合类型的推荐实现方案

PL/pgSQL提供两种常用写法实现多结果组装返回，你可以根据自己的逻辑复杂度选择：

方案1：使用RETURN NEXT逐行构建结果集 #

适合需要循环处理、组合多个查询/函数执行结果的场景，灵活性最高。你可以先声明一个PERSON_FOUND类型的变量暂存单行数据，每次组装完成后调用RETURN NEXT将该行加入结果集，全部处理完成后直接调用RETURN结束函数即可。
示例实现如下：

CREATE FUNCTION find_person_by_email(p_email TEXT,
                            p_names_rec RECORD,
                            p_phone TEXT,
                            p_document TEXT,
                            p_date_of_birth TIMESTAMPTZ)
RETURNS SETOF PERSON_FOUND AS $$                            
DECLARE  
  -- 声明暂存单行结果的变量
  v_found PERSON_FOUND;
  -- 替换为你自己的条件判断逻辑
  conditions_satisfied BOOLEAN := TRUE;
BEGIN
  IF NOT conditions_satisfied THEN
    -- 无匹配结果直接返回空集即可，不需要写RETURN NULL
    RETURN;
  END IF;

  -- 示例：遍历第一个查询结果组装返回行
  FOR v_found.person_id, v_found.last_update IN 
    SELECT id, update_time FROM person_basic 
    WHERE email = p_email
  LOOP
    -- 自定义计算rank、confidence字段
    v_found.rank := 1;
    v_found.confidence := 'HIGHEST';
    -- 将当前行加入结果集
    RETURN NEXT v_found;
  END LOOP;

  -- 示例：调用其他函数获取结果并追加
  FOR v_found IN 
    SELECT * FROM find_person_by_phone(p_phone)
  LOOP
    -- 可按需修改字段值后再追加
    v_found.rank := v_found.rank + 10;
    RETURN NEXT v_found;
  END LOOP;

  -- 示例：手动组装单行结果追加
  v_found.person_id := '你自定义生成的UUID'::UUID;
  v_found.rank := 99;
  v_found.confidence := 'LOW';
  v_found.last_update := NOW();
  RETURN NEXT v_found;

  -- 所有行添加完成后结束函数
  RETURN;
END
$$  LANGUAGE plpgsql;

方案2：使用RETURN QUERY直接追加查询结果 #

如果你的结果可以直接通过SQL查询组装，不需要逐行做逻辑处理，用RETURN QUERY写法更简洁，支持多次调用追加多个查询的结果：

CREATE FUNCTION find_person_by_email(p_email TEXT,
                            p_names_rec RECORD,
                            p_phone TEXT,
                            p_document TEXT,
                            p_date_of_birth TIMESTAMPTZ)
RETURNS SETOF PERSON_FOUND AS $$                            
DECLARE  
  conditions_satisfied BOOLEAN := TRUE;
BEGIN
  IF NOT conditions_satisfied THEN
    RETURN;
  END IF;

  -- 追加第一个查询的结果
  RETURN QUERY
    SELECT 
      id AS person_id,
      1 AS rank,
      'HIGHEST'::CONFIDENCE AS confidence,
      update_time AS last_update
    FROM person_basic
    WHERE email = p_email;

  -- 追加第二个查询的结果
  RETURN QUERY
    SELECT 
      person_id,
      rank + 10 AS rank,
      confidence,
      last_update
    FROM find_person_by_phone(p_phone);

  -- 追加手动构造的单行结果
  RETURN QUERY
    SELECT 
      '自定义UUID'::UUID AS person_id,
      99 AS rank,
      'LOW'::CONFIDENCE AS confidence,
      NOW() AS last_update;

  RETURN;
END
$$  LANGUAGE plpgsql;

注意事项 #

• 无匹配结果时直接调用RETURN返回空结果集即可，符合SELECT * FROM 函数()的使用习惯，不要写RETURN NULL
• 多次调用RETURN NEXT或RETURN QUERY的结果会自动按调用顺序拼接为完整结果集返回
• 枚举类型赋值时建议加::CONFIDENCE显式类型转换，避免类型不匹配报错

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