如何用PostgreSQL游标分批取数，实现分页并返回全列？

问题根源 #

你之前的函数每次返回相同100条deal_id，原因是每次调用都重新初始化查询，没有维护批次位置，同时仅指定deal_id字段，导致无法返回全列数据。

方案一：使用会话级游标（适合同会话内分批） #

通过创建PL/pgSQL函数，利用PostgreSQL的会话级游标保持批次位置，每次调用返回下一批100条全列数据：

CREATE OR REPLACE FUNCTION fetch_compare_prices_batch()
RETURNS SETOF compareprices AS $$
DECLARE
    batch_cursor CURSOR FOR SELECT * FROM compareprices ORDER BY deal_id; -- 必须排序保证批次稳定
    rec compareprices%ROWTYPE;
    batch_size INT := 100;
    fetched_count INT := 0;
BEGIN
    -- 检查游标是否已打开，未打开则初始化
    IF NOT EXISTS (SELECT 1 FROM pg_cursors WHERE name = 'batch_cursor') THEN
        OPEN batch_cursor;
    END IF;

    -- 抓取当前批次的100条数据
    LOOP
        FETCH NEXT FROM batch_cursor INTO rec;
        EXIT WHEN NOT FOUND OR fetched_count >= batch_size;
        
        RETURN NEXT rec;
        fetched_count := fetched_count + 1;
    END LOOP;

    -- 数据取完后关闭游标
    IF NOT FOUND THEN
        CLOSE batch_cursor;
    END IF;
END;
$$ LANGUAGE plpgsql;

关键说明 #

• SELECT *确保返回表的全列数据
• ORDER BY deal_id是必须的：保证批次顺序稳定，避免重复或遗漏（如果deal_id不是唯一键，改用其他唯一排序字段）
• 游标状态在当前数据库会话中保持，每次调用函数会接着上一次的位置继续取数
• 调用方式：每次执行SELECT * FROM fetch_compare_prices_batch();，直到返回空结果则表示所有数据已取完

方案二：基于唯一键的分页（适合无状态/跨会话场景） #

如果不需要依赖会话游标，可在应用端维护上一批次的最后一条deal_id，每次查询从该ID之后取数：

-- 首次查询（取前100条）
SELECT * FROM compareprices ORDER BY deal_id LIMIT 100;

-- 后续查询（替换为上一批的最后deal_id）
SELECT * FROM compareprices WHERE deal_id > [上一批最后deal_id] ORDER BY deal_id LIMIT 100;

优势 #

• 不依赖会话状态，适合分布式系统或跨会话调用
• 避免游标快照带来的一致性问题（如果表数据频繁更新，此方式能获取最新数据）

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