Fetching multiple rows: more than one row returned by a subquery used as an expression

Question

I searched all posts regarding this issue: more than one row returned by a subquery used as an expression.

But my case I need to fetch multiple rows for my purpose.

CREATE OR REPLACE FUNCTION get_friend_diaries (contacts text[], "intPage" int, "intOffset" int) 
RETURNS jsonb[] 
AS $$ 
DECLARE
    result jsonb[];
BEGIN
    -- Your SQL logic here
    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', (SELECT json_agg(jsonb_build_object(
                                'image', i.image
                            )) 
                      FROM images as i 
                      WHERE i."diaryId" = d."diaryId")
        ))
        FROM diaries d
        JOIN users u ON d."userId" = u."userId"
        WHERE u.phone = ANY(contacts) 
        AND d."secretType" <> '비공개' 
        GROUP BY d."createdAt"
        ORDER BY d."createdAt" DESC 
        LIMIT "intOffset" OFFSET ("intPage" - 1)
    );
    
    RETURN result;
END;
$$ LANGUAGE plpgsql;

So Fetching more than one row is my purpose And I set return type as json[] to get array.

But whay this error happens? and how to fix it?

Answer 1

Your function isn't using any plpgsql specific features, so you can just use plain SQL like this:

CREATE OR REPLACE FUNCTION get_friend_diaries (contacts text[], "intPage" int, "intOffset" int) 
RETURNS jsonb[]
AS $$ 
    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', (SELECT json_agg(jsonb_build_object(
                            'image', i.image
                        )) 
                    FROM images as i 
                    WHERE i."diaryId" = d."diaryId")
    ))
    FROM diaries d
    JOIN users u ON d."userId" = u."userId"
    WHERE u.phone = ANY(contacts) 
    AND d."secretType" <> '비공개' 
    GROUP BY d."createdAt"
    ORDER BY d."createdAt" DESC 
    LIMIT "intOffset" OFFSET ("intPage" - 1);
$$ LANGUAGE sql;