PostgreSQL如何在函数执行过程中返回可查看的中间结果

默认情况下PL/pgSQL会对RETURN NEXT返回的结果做内部缓冲，同时绝大多数PostgreSQL客户端默认会等待查询全量返回后再输出结果，所以才会出现调用函数后等待1000秒才能看到全部结果的情况。可通过以下两种方案实现逐行输出中间结果：

方案1：显式游标调用（兼容所有PostgreSQL版本） #

无需修改原有函数，调用时通过显式游标逐行获取结果即可：

-- 开启事务（游标必须在事务块内使用）
BEGIN;
-- 声明绑定目标函数的游标
DECLARE cur_foo CURSOR FOR SELECT * FROM get_all_foo();
-- 每执行1次FETCH即可拿到最新生成的1条结果，间隔1秒
FETCH NEXT FROM cur_foo;
-- 如需批量获取可调整fetch数量，例如下方语句一次获取10条结果
-- FETCH 10 FROM cur_foo;
-- 执行完毕后关闭游标并提交事务
CLOSE cur_foo;
COMMIT;

如果使用psql客户端，可直接设置\set FETCH_COUNT 1，开启逐行拉取配置，此时直接执行SELECT * FROM get_all_foo();即可看到逐秒输出的结果。

方案2：关闭PL/pgSQL结果缓冲（PostgreSQL 11及以上版本） #

直接在原有函数内添加配置项，禁用RETURN NEXT的内部缓冲逻辑，修改后的函数如下：

CREATE OR REPLACE FUNCTION get_all_foo() RETURNS SETOF int AS
$BODY$
DECLARE
    i int;
begin
    -- 禁用结果缓冲，每一次RETURN NEXT都会立即向上层返回结果
    SET plpgsql.return_next_buffering = off;
    i=1;
    LOOP
        RETURN NEXT f(i);--assume f() cost one second to return
        i=i+1;
        EXIT when i>1000;
    END LOOP;
    RETURN;
END
$BODY$
LANGUAGE plpgsql;

修改后配合客户端的逐行拉取配置，即可实现逐秒输出中间结果的效果。

注意事项 #

如果使用JDBC、psycopg2等程序驱动调用，需要关闭驱动默认的全量结果集缓冲配置，例如psycopg2使用命名游标实现逐行拉取，JDBC设置fetchSize为大于0的数值即可。

内容的提问来源于stack exchange，提问作者H.W