PostgreSQL自定义函数报错：子查询返回多行，需取多行如何解决？

错误原因 #

• 你添加了GROUP BY d."createdAt"，这会将同一创建时间的日记分组，每组生成一个jsonb数组，最终外层SELECT会返回多行结果（每个分组对应一行）。
• 而result := (SELECT ...)这种赋值方式要求子查询必须返回单一行，当子查询返回多行时，就会触发"more than one row returned by a subquery used as an expression"错误。
• 更关键的是，你的业务目标是获取分页后的多行日记并打包成一个jsonb数组返回，GROUP BY完全是多余的，反而打乱了预期逻辑。

修复步骤 #

1. 移除多余的GROUP BY d."createdAt"，直接将所有符合条件的分页日记聚合成一个数组。
2. 修正分页逻辑（原逻辑偏移量计算错误）：将OFFSET ("intPage" - 1)改为OFFSET ("intPage" - 1) * "intOffset"，确保分页偏移量正确。

修复后的完整代码 #

CREATE OR REPLACE FUNCTION get_friend_diaries (contacts text[], "intPage" int, "intOffset" int) 
RETURNS jsonb[] 
AS $$ 
DECLARE
    result jsonb[];
BEGIN
    result := (
        SELECT array_agg(jsonb_build_object(
            'numComments', d."numComments",
            'diaryId', d."diaryId",
            'secretType', d."secretType",
            'userId', d."userId",
            'todayDiary', d."todayDiary",
            'todayMood', d."todayMood",
            'numLikes', d."numLikes",
            'createdAt', d."createdAt",
            'notice', d.notice,
            'forceSecret', d."forceSecret",
            'birthDay', u."birthDay",
            'gender', u.gender,
            'loginType', u."loginType",
            'avatar', u.avatar,
            'phone', u.phone,
            'birthYear', u."birthYear",
            'name', u.name,
            'fcmToken', u."fcmToken",
            'stepStatus', u."stepStatus",
            'lastVisit', u."lastVisit",
            'subdistrictId', u."subdistrictId",
            'targetDailyStep', u."targetDailyStep",
            'images', COALESCE((SELECT json_agg(jsonb_build_object(
                                'image', i.image
                            )) 
                      FROM images as i 
                      WHERE i."diaryId" = d."diaryId"), '[]'::jsonb)
        ))
        FROM diaries d
        JOIN users u ON d."userId" = u."userId"
        WHERE u.phone = ANY(contacts) 
        AND d."secretType" <> '비공개' 
        ORDER BY d."createdAt" DESC 
        LIMIT "intOffset" OFFSET ("intPage" - 1) * "intOffset"
    );
    
    RETURN result;
END;
$$ LANGUAGE plpgsql;

额外优化说明 #

• 给images字段的子查询加上COALESCE，确保没有图片的日记返回空数组[]而非null，返回结果更统一。
• 建议将参数"intOffset"改名为"intLimit"，因为它代表的是每页数据量，用Limit命名更符合常规语义，避免混淆。

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