PostgreSQL PL/pgSQL函数如何高效返回表数据 避免全量缓存结果

核心问题说明 #

PL/pgSQL中默认的RETURN QUERY和RETURN NEXT确实会在低版本PostgreSQL中缓存全量结果集后再返回给调用方，和Oracle的RETURN PIPELINED流式返回逻辑有明显差异，以下是可落地的替代方案：

方案1：使用refcursor游标实现流式返回（全版本支持） #

该方案完全不需要在函数侧缓存结果，数据直接从源表流式传输到调用方，性能损耗最低：

• 函数定义示例：

CREATE OR REPLACE FUNCTION wait_and_query(target_table text) 
RETURNS refcursor AS $$
DECLARE
    res_cur refcursor := 'query_result_cursor';
BEGIN
    -- 轮询等待表录入系统目录
    LOOP
        IF EXISTS (
            SELECT 1 FROM information_schema.tables 
            WHERE table_name = target_table
            -- 建议加上schema过滤条件，避免同名表误匹配
            -- AND table_schema = 'your_schema'
        ) THEN
            EXIT;
        END IF;
        PERFORM pg_sleep(0.1); -- 可根据场景调整轮询间隔
    END LOOP;
    -- 仅绑定查询到游标，不拉取数据
    OPEN res_cur FOR EXECUTE format('SELECT * FROM %I', target_table);
    RETURN res_cur;
END;
$$ LANGUAGE plpgsql VOLATILE;

• 调用方式：

-- 需在事务内使用游标
BEGIN;
SELECT wait_and_query('your_table_name');
-- 支持逐行获取或者批量获取，完全流式处理
FETCH ALL IN query_result_cursor;
COMMIT;

方案2：原生PIPELINED函数（PostgreSQL 14+ 支持） #

PostgreSQL 14及以上版本原生支持PIPELINED修饰符，逻辑和Oracle的管道函数完全一致，不需要全量缓存结果：

CREATE OR REPLACE FUNCTION wait_and_query_pipelined(target_table text)
RETURNS SETOF record PIPELINED AS $$
DECLARE
    row_rec record;
BEGIN
    -- 轮询等待表创建完成
    LOOP
        IF EXISTS (
            SELECT 1 FROM information_schema.tables 
            WHERE table_name = target_table
        ) THEN
            EXIT;
        END IF;
        PERFORM pg_sleep(0.1);
    END LOOP;
    -- 逐行返回，每生成一行就发送给调用方，无全量缓存
    FOR row_rec IN EXECUTE format('SELECT * FROM %I', target_table) LOOP
        RETURN NEXT row_rec;
    END LOOP;
    RETURN;
END;
$$ LANGUAGE plpgsql VOLATILE;

方案3：客户端侧轮询（最高性能方案） #

如果可以调整业务逻辑，推荐将轮询逻辑放到客户端实现：

• 客户端循环查询系统表判断目标表是否存在
• 表创建完成后直接发起SELECT * FROM 表名查询，PostgreSQL服务端默认会流式返回查询结果，跳过函数层的额外开销，性能最优。

注意：所有涉及动态表名的查询都需要使用format('%I', 表名)做标识符转义，避免SQL注入风险。

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