PostgreSQL函数求助：如何让函数返回查询到的所有ID数组

你想要实现的是：输入一个ID，找到该ID对应的sab_subject分组下的所有ID，然后以整数数组的形式返回结果。当前写法存在几个关键问题，我来帮你修正：

问题分析 #

1. 返回类型不匹配：你想要返回数组，但最初声明的返回类型是SETOF integer（多行整数），如果要返回数组，正确的返回类型应该是integer[]。
2. 聚合语法错误：PostgreSQL中没有COLLECT这个聚合函数，要将多行ID合并成数组，应该用array_agg()。
3. 单变量存储限制：如果把all_id声明为integer，它只能存储单个值，自然只能返回最后一条查询结果。

正确写法（返回整数数组） #

这个版本的函数会直接返回包含所有目标ID的数组：

CREATE OR REPLACE FUNCTION public.all_id(prm_id integer) 
RETURNS integer[] 
LANGUAGE plpgsql 
COST 100.0 
AS $function$
DECLARE
    all_id integer[];
BEGIN
    -- 使用array_agg聚合所有ID为数组
    SELECT array_agg(id)
    INTO all_id
    FROM subject_data
    WHERE sab_subject = (
        SELECT sab_subject 
        FROM subject_data 
        WHERE id = prm_id
    );
    
    RETURN all_id;
END;
$function$;

调用方式：

SELECT public.all_id(1);

返回结果会是类似{1,3,5}这样的整数数组。

备选：返回多行整数（如果需要） #

如果你其实希望每行返回一个ID（对应你最初用的SETOF integer），可以用RETURN QUERY直接返回查询结果，不需要手动声明变量：

CREATE OR REPLACE FUNCTION public.all_id(prm_id integer) 
RETURNS SETOF integer 
LANGUAGE plpgsql 
COST 100.0 
AS $function$
BEGIN
    RETURN QUERY
        SELECT id
        FROM subject_data
        WHERE sab_subject = (
            SELECT sab_subject 
            FROM subject_data 
            WHERE id = prm_id
        );
END;
$function$;

调用方式：

SELECT * FROM public.all_id(1);

会返回多行，每行一个ID值。

内容的提问来源于stack exchange，提问作者Žilvinas Ringelė