取出每组的前 n 条 记录, 来个高效的写法

drop function if exists fun_tests(bigint);
create or replace function fun_tests(bigint default 5)
  returns table(pid bigint,file_name text)
as $$
    declare
        v_len integer;
        v_parentids bigint[];
        v_rec record;
    begin
        v_parentids := (array[100000,100001])::bigint[];
        v_len := array_length(v_parentids, 1);
        for i in 1..v_len loop  
            /*写法1*/
            /*for pid,file_name in select parentid,fname 
                from attachs where parentid=v_parentids[i] order by objectid desc limit $1 loop
                return next;
            end loop;
            */

            /*写法2*/
            for v_rec in select parentid,fname 
                from attachs where parentid=v_parentids[i] order by objectid desc limit $1 loop
                pid:=v_rec.parentid;
                file_name:=v_rec.fname;
                return next;
            end loop;
        end loop;
    end;
$$ language plpgsql strict;

使用方法

select * from fun_tests();
select * from fun_tests(10);