如何让PostgreSQL实现类似Oracle的游标惰性求值并返回错误前数据？

我需要复刻Oracle中的一个函数，原Oracle函数代码如下：

OPEN pcursor FOR v_sql;
loop
    fetch pcursor into
        out_rec.XYZ
    ;

    exit when pcursor%NOTFOUND;

             INSERT INTO tab1 VALUES (out_rec.XYZ);
             COMMIT;
    pipe row(out_rec);
end loop;
EXCEPTION
    WHEN OTHERS THEN
        v_err_code := SQLCODE;

存储在v_sql中的查询存在问题：部分特定行数据会导致执行失败。在SQL Developer中执行该查询会返回部分数据，但获取完整结果集或执行count(*)时会报错，原因是某行数据存在问题。该Oracle函数的行为是返回错误前的所有数据，之后终止执行，这得益于其异常处理逻辑会捕获异常并不做额外处理。

现在我尝试在PostgreSQL中复刻该函数，遇到两个问题：

• 无法实现查询的惰性求值，只要执行查询就直接失败，无论是否获取全部数据。
• 我编写了如下代码模拟游标行为：

CREATE OR REPLACE FUNCTION test_fn()
 RETURNS table (t ret_data_type)
 LANGUAGE plpgsql
AS $function$
...
for t in execute v_sql
    loop
        return next;
    end loop;
    exception when others then
        raise notice 'error';
...

这段代码在无错误数据时运行正常，但只要数据集存在错误，就会直接失败且不返回任何数据。

我的问题是：PostgreSQL是否支持惰性求值（逐行/分批获取数据），从而可以返回错误前的所有数据？

PostgreSQL支持惰性求值，但你当前使用的FOR ... IN EXECUTE语法会在进入循环前尝试解析并获取整个结果集，所以一旦结果集中有错误行，整个查询会直接失败，无法返回任何数据。要实现类似Oracle的逐行处理并返回错误前数据的效果，需要使用显式游标来逐行获取数据，在每一行处理完成后立即返回，遇到异常时终止并保留已返回的数据。

以下是修改后的PostgreSQL函数示例：

CREATE OR REPLACE FUNCTION test_fn()
RETURNS TABLE (t ret_data_type)
LANGUAGE plpgsql
AS $function$
DECLARE
    v_sql text := '你的查询语句'; -- 替换为实际的v_sql内容
    cur refcursor;
    rec ret_data_type;
BEGIN
    -- 打开显式游标
    OPEN cur FOR EXECUTE v_sql;
    
    LOOP
        -- 逐行获取数据
        FETCH cur INTO rec;
        EXIT WHEN NOT FOUND;
        
        -- 执行插入操作（和Oracle原逻辑对齐）
        INSERT INTO tab1 VALUES (rec.XYZ);
        COMMIT;
        
        -- 返回当前行
        t := rec;
        RETURN NEXT;
    END LOOP;
    
    CLOSE cur;
EXCEPTION
    WHEN OTHERS THEN
        -- 捕获异常，关闭游标
        IF cur IS NOT NULL THEN
            CLOSE cur;
        END IF;
        -- 记录错误信息，和Oracle逻辑一致不中断已返回数据
        RAISE NOTICE '执行错误，错误码: %', SQLSTATE;
END;
$function$;

关键说明 #

• 显式游标OPEN ... FOR EXECUTE会启动查询但不会立即获取所有行，而是在每次FETCH时才获取下一行，实现真正的惰性求值。
• 每执行一次RETURN NEXT，当前行的数据就会立即返回给调用者，即使后续遇到异常，已返回的行也不会丢失。
• 异常处理块中可以选择关闭游标、记录错误信息，和Oracle原逻辑对齐，不影响已返回数据的传递。

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