PostgreSQL中按指定类别数组随机获取对应物品的函数实现及报错解决咨询

我来帮你搞定这个问题！先说说你遇到的报错原因：ERROR: subquery must return only one column，这是因为你在EXECUTE里用SELECT *返回了整行数据，但直接把它赋值给数组变量的方式不对——PL/pgSQL没法直接把多列的查询结果塞进数组，而且你后续返回结果的逻辑也有小问题。

下面给你两种可行的解决方案，从修正原有逻辑到更高效的优化版本都有：

方案一：修正原有PL/pgSQL循环逻辑 #

这个方案保留你原来的循环思路，调整变量处理和结果返回的方式：

CREATE OR REPLACE FUNCTION public.get_random_items(IN args uuid[]) 
RETURNS SETOF items 
LANGUAGE 'plpgsql' 
VOLATILE PARALLEL UNSAFE COST 100 ROWS 1000 
AS $BODY$
DECLARE
    cat_id uuid;
    output items[];
    temp_row items; -- 新增行变量，临时存储单条查询结果
BEGIN
    FOREACH cat_id IN ARRAY args LOOP
        -- 先把单个分类的随机物品行存入临时变量
        SELECT * INTO temp_row
        FROM items 
        WHERE category_id = cat_id 
        ORDER BY random() 
        LIMIT 1;
        
        -- 将临时行追加到结果数组中
        output := output || temp_row;
    END LOOP;
    
    -- 展开数组，返回结果集
    RETURN QUERY SELECT unnest(output);
END $BODY$;

关键调整点： #

• 新增temp_row行变量，用来接收每次查询的单条结果，避免直接把多列数据塞数组的错误
• 用output := output || temp_row语法将行数据追加到数组
• 最后用unnest(output)把数组展开成符合要求的结果集返回

方案二：更高效的SQL窗口函数实现 #

如果输入的分类ID数量较多，循环会多次执行查询，效率偏低。推荐用窗口函数的方式，一次性查询所有分类的随机物品：

CREATE OR REPLACE FUNCTION public.get_random_items(IN args uuid[]) 
RETURNS SETOF items 
LANGUAGE sql 
VOLATILE PARALLEL UNSAFE COST 100 ROWS 1000 
AS $BODY$
    SELECT *
    FROM (
        SELECT 
            *,
            -- 按分类分组，每组内随机排序并编号
            ROW_NUMBER() OVER (PARTITION BY category_id ORDER BY random()) AS rn
        FROM items
        -- 匹配所有输入的分类ID
        WHERE category_id = ANY(args)
    ) sub
    -- 取每组的第一条（即每个分类的随机物品）
    WHERE rn = 1;
$BODY$;

这个方法只需要执行一次查询，性能比循环好很多，逻辑也更简洁。

现在你再执行SELECT * FROM get_random_items('{d59f2779-4868-439c-96eb-b5a735135379}')，就能正常返回对应分类的随机物品了。

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