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

Question

我正在尝试在 plpgsql 中创建一个函数，例如:

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;

想法是获取大于输入照片 ID 的照片 ID。如果没有。结果中的照片数量少于限制，我将尝试获取底部的 n 条记录。

以上功能无效。有人可以就如何将选择查询的结果存储在变量中提供一些指示/提示或链接。

注意:photo_id 是一种 bigint[] 数据类型 - 我的意思是它是故意的 bigint[]。

Answer 1

您需要添加一个 array_agg到

1. 从您的查询中获取单个值，以便 SELECT...INTO 起作用。
2. 并获得 bigint[] 结果以放入您的 bigint[] 目标。

然后您需要添加一个派生表以使您的 LIMIT 起作用。

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;

然后你可以询问数组有多大而不是查看 ROW_COUNT:

photo_count := array_length(photo_ids, 1);

然后下一个 SELECT...INTO 将类似于上面的新 array_agg 版本。