PL/pgSQL: Can we store a query result into a variable

Question

I'm trying to create a function in plpgsql like:

CREATE OR REPLACE FUNCTION select_left_photo_ids(in_photo_id bigint[], in_album_id bigint, in_limit int) RETURNS SETOF bigint[] AS
$$
DECLARE photo_count int;
DECLARE photo_ids bigint[];
    BEGIN
        SELECT photo_id INTO STRICT photo_ids FROM tbl_album_photos WHERE album_id = in_album_id AND photo_id < in_photo_id ORDER BY photo_id DESC LIMIT in_limit;
        GET DIAGNOSTICS photo_count = ROW_COUNT;
        IF photo_count < in_limit THEN
            SELECT photo_id INTO STRICT photo_ids FROM (SELECT photo_id FROM tbl_album_photos WHERE album_id = in_album_id ORDER BY photo_id LIMIT in_limit) AS dummy ORDER BY photo_id DESC;
        END IF;
        RETURN photo_ids;
    END;
$$
LANGUAGE plpgsql;

The idea is to fetch the photo ids that are greater than input photo ids. If the no. of photos in the result comes out to be less than limit, I will try to fetch bottom n records.

The above function does not work. Could somebody please give a some pointers/hints or links on how to store result of a select query in a variable.

Note: photo_id is a bigint[] data type - I mean its intentionally bigint[].

Answer 1

You need to add an array_agg to

1. Get a single value from your query so that SELECT...INTO will work.
2. And get a bigint[] result to put into your bigint[] target.

Then you'll need to add a derived table to get your LIMIT to work.

select array_agg(photo_id) into strict photo_ids
from (
    select photo_id
    from tbl_album_photos
    where album_id = in_album_id
      and photo_id < in_photo_id
    order by photo_id desc
    limit in_limit
) dt;

Then you can ask the array how big it is instead of looking at ROW_COUNT:

photo_count := array_length(photo_ids, 1);

And then the next SELECT...INTO would be similar to the new array_agg version above.