如何正确在plpgsql函数中使用DECLARE语句，解决查询无结果目标数据报错

问题原因 #

你的报错核心是以下两点：

1. 函数定义了返回SETOF sponsor_data（多行结果集合），但你用SELECT ... INTO 单个变量的写法只能接收单行查询结果，当查询返回多行时plpgsql无法完成赋值，就会抛出该错误。
2. 你声明了register_date、sponsor_name两个入参，但关联查询中没有用这两个参数做过滤，直接返回全表关联的所有数据，必然出现多行结果触发上述报错。
   另外还有两处隐藏问题：

• SELECT列表中tb_sponsor.email email_type是错误的别名写法，你需要保证SELECT返回的列顺序、字段类型完全匹配你自定义的sponsor_data类型定义，否则赋值会失败。
• 你DECLARE部分声明的大量变量完全没有使用，属于冗余代码。

修复方案 #

推荐直接使用RETURN QUERY语法实现返回集合的需求，无需额外声明变量，写法更简洁：

SET search_path to olympic;

CREATE OR REPLACE FUNCTION fn_get_info_by_sponsor
       (p_register_date tb_register.register_ts%type, p_sponsor_name tb_sponsor.name%type)
RETURNS SETOF sponsor_data AS $$
BEGIN 
      RETURN QUERY
      SELECT 
           tb_finance.sponsor_name,
           tb_sponsor.email,
           tb_athlete.name AS athlete_name,
           tb_discipline.name AS discipline_name,
           tb_register.round_number,
           tb_register.register_measure,
           tb_register.register_position,
           tb_register.register_ts
      FROM olympic.tb_sponsor
      INNER JOIN olympic.tb_finance
           ON tb_finance.sponsor_name = tb_sponsor.name
      INNER JOIN olympic.tb_athlete
           ON tb_athlete.athlete_id = tb_finance.athlete_id
      INNER JOIN olympic.tb_register
           ON tb_register.athlete_id = tb_athlete.athlete_id
      INNER JOIN olympic.tb_discipline
           ON tb_discipline.discipline_id = tb_register.discipline_id
      -- 入参过滤条件，避免返回全量数据
      WHERE tb_sponsor.name = p_sponsor_name
        AND tb_register.register_ts >= p_register_date
      ORDER BY tb_register.register_ts;
END;
$$ LANGUAGE plpgsql;

如果需要保留你原有的变量赋值写法，可改用FOR循环遍历多行结果：

CREATE OR REPLACE FUNCTION fn_get_info_by_sponsor
       (p_register_date tb_register.register_ts%type, p_sponsor_name tb_sponsor.name%type)
RETURNS SETOF sponsor_data AS $$
DECLARE 
 data_sponsor sponsor_data;
BEGIN 
      FOR data_sponsor IN
          SELECT 
               tb_finance.sponsor_name,
               tb_sponsor.email,
               tb_athlete.name AS athlete_name,
               tb_discipline.name AS discipline_name,
               tb_register.round_number,
               tb_register.register_measure,
               tb_register.register_position,
               tb_register.register_ts
          FROM olympic.tb_sponsor
          INNER JOIN olympic.tb_finance
               ON tb_finance.sponsor_name = tb_sponsor.name
          INNER JOIN olympic.tb_athlete
               ON tb_athlete.athlete_id = tb_finance.athlete_id
          INNER JOIN olympic.tb_register
               ON tb_register.athlete_id = tb_athlete.athlete_id
          INNER JOIN olympic.tb_discipline
               ON tb_discipline.discipline_id = tb_register.discipline_id
          WHERE tb_sponsor.name = p_sponsor_name
            AND tb_register.register_ts >= p_register_date
          ORDER BY tb_register.register_ts
      LOOP
          RETURN NEXT data_sponsor;
      END LOOP;
      RETURN;
END;
$$ LANGUAGE plpgsql;

修复完成后调用方式不变：

SELECT * FROM fn_get_info_by_sponsor('2021-06-02 00:00:00','Reebok');

注：入参的过滤逻辑可根据你的实际业务需求调整，上述示例默认取大于等于传入注册日期、匹配赞助商名称的结果。

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