PostgreSQL返回SETOF自定义类型函数空返回及报错解决方案

问题原因 #

• 返回空值的核心触发场景有两个：
  1. 当传入的乐队名称不存在时，代码走到RAISE NOTICE 'Not exists'分支，没有对result_return赋值就直接执行了RETURN NEXT result_return，返回了未初始化的空自定义类型，就会出现,,,,的结果
  2. 就算乐队存在，最后给result_return赋值的查询如果没有符合条件的结果（比如没有时长小于3分钟的歌曲、无在世成员等），SELECT INTO会直接将result_return置为空，最终也会返回空行
• 你尝试改成RETURN result_return报错是因为函数定义了RETURNS SETOF，这种返回集合的函数不能直接用带参数的RETURN，只能用RETURN NEXT逐行返回或者RETURN QUERY返回查询结果集；如果确定每次只返回1行结果，也可以把返回值改成直接返回report_band_type而不是SETOF类型。

修复方案 #

方案1：简化逻辑，用RETURNING子句避免重复查询（推荐） #

你原来的代码里重复写了三次完全相同的统计查询，非常冗余，直接用INSERT/UPDATE的RETURNING子句就能直接拿到修改后的数据，不需要额外再查一次：

CREATE OR REPLACE FUNCTION ubd_20211.update_report_band(p_name VARCHAR(255))
RETURNS ubd_20211.report_band_type -- 确定只返回1行可以去掉SETOF，直接支持RETURN返回结果
LANGUAGE plpgsql 
AS $$
DECLARE 
    id_band_result integer;
    result_return ubd_20211.report_band_type;
BEGIN 
    -- 先查询对应乐队ID，不存在直接返回空
    SELECT id_band INTO id_band_result 
    FROM ubd_20211.band
    WHERE LOWER(name) = LOWER(p_name);
    
    IF NOT FOUND THEN
        RAISE NOTICE 'Not exists';
        RETURN NULL;
    END IF;

    -- 用INSERT ... ON CONFLICT 合并更新和插入逻辑，无需单独判断存在性
    INSERT INTO ubd_20211.REPORT_BAND (id_band, num_instruments, num_members_alive, longest_album_title, num_short_songs) 
    SELECT 
        B.id_band, 
        COUNT(MEM.instrument) AS num_instruments, 
        COUNT(MUS.name) AS num_members_alive, 
        AL.title, 
        COUNT(S.duration) AS num_short_songs                    
    FROM ubd_20211.BAND AS B
    INNER JOIN ubd_20211.MEMBER AS MEM ON B.id_band = MEM.id_band 
    INNER JOIN ubd_20211.MUSICIAN AS MUS ON MEM.id_musician = MUS.id_musician 
    INNER JOIN ubd_20211.ALBUM AS AL ON B.id_band = AL.id_band 
    INNER JOIN ubd_20211.SONG AS S ON AL.id_album = S.id_album 
    WHERE 
        B.id_band = id_band_result 
        AND S.duration < '00:03:00' -- 原时间写法不规范，补上前导零避免解析错误
        AND MUS.death is NULL 
        AND AL.title = (
            SELECT title FROM ubd_20211.BAND AS B2
            INNER JOIN ubd_20211.ALBUM AS AL2 ON B2.id_band = AL2.id_band
            WHERE B2.id_band = B.id_band
            ORDER BY LENGTH(AL2.title) DESC LIMIT 1
    )               
    GROUP BY 1,4
    ON CONFLICT (id_band) DO UPDATE -- 主键冲突时执行更新
    SET 
        num_instruments = EXCLUDED.num_instruments,
        num_members_alive = EXCLUDED.num_members_alive,
        longest_album_title = EXCLUDED.longest_album_title,
        num_short_songs = EXCLUDED.num_short_songs
    RETURNING 
        id_band as t_id_band,
        num_instruments as t_num_instruments,
        num_members_alive as t_num_members_alive,
        longest_album_title as t_longest_album_title,
        num_short_songs as t_num_short_songs
    INTO result_return; -- 直接把RETURNING的结果赋值给返回变量

    RETURN result_return;
END;
$$;

方案2：保留原有结构只修复空返回问题 #

如果你不想改动原有逻辑，只需要在RETURN NEXT之前判断result_return是否有值即可：

-- 替换原来末尾的 RETURN NEXT result_return; 逻辑
IF band_exists AND result_return.t_id_band IS NOT NULL THEN
    RETURN NEXT result_return;
END IF;
RETURN; -- 没有符合条件的结果就直接返回空集合

额外提示 #

你原代码中的时间条件S.duration < '00:3:00'写法不规范，建议统一改成'00:03:00'避免PostgreSQL解析时间出错。

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