PostgreSQL函数内声明带WITH HOLD的游标报错问题咨询

嘿，这个问题我之前在项目里碰到过，PostgreSQL对函数内使用WITH HOLD游标确实有明确的限制，咱们来拆解原因和可行的解决方案：

核心原因 #

在PostgreSQL 10.3中，普通SQL/PL/pgSQL函数不允许创建WITH HOLD游标，背后的逻辑是：

• WITH HOLD游标是跨事务存活的，只有当创建它的事务提交后，游标才能被后续事务访问；
• 普通函数是运行在调用者的事务上下文里的，函数内部无法主动执行COMMIT或ROLLBACK（PostgreSQL 11才引入支持事务控制的PROCEDURE，10.3没有这个特性）；
• 如果在函数里创建WITH HOLD游标，函数结束时外层事务还未提交，游标无法进入"持有"状态，直接触发报错。

你大概率会看到类似这样的错误提示：

ERROR:  cannot declare cursor WITH HOLD inside a function

可行的解决方案 #

针对10.3版本的限制，有几种替代方案能实现类似需求：

1. 让调用者负责创建WITH HOLD游标 #

函数只返回需要执行的查询语句，由外部调用者来创建带WITH HOLD的游标：

-- 定义函数返回查询语句
CREATE OR REPLACE FUNCTION get_target_query() RETURNS text AS $$
BEGIN
    RETURN 'SELECT * FROM your_table WHERE your_condition';
END;
$$ LANGUAGE plpgsql;

调用时手动创建游标并提交事务：

BEGIN;
-- 声明带WITH HOLD的游标
DECLARE your_cursor CURSOR WITH HOLD FOR EXECUTE (SELECT get_target_query());
COMMIT; -- 提交后游标跨事务存活
-- 后续可以在任意事务中访问游标
FETCH NEXT FROM your_cursor;

2. 使用临时表替代游标（如果业务允许） #

如果你的需求是跨事务保留查询结果，ON COMMIT PRESERVE ROWS的临时表可以达到类似效果：

CREATE OR REPLACE FUNCTION save_query_result() RETURNS void AS $$
BEGIN
    -- 创建临时表，事务提交后保留数据
    CREATE TEMP TABLE temp_result ON COMMIT PRESERVE ROWS AS
    SELECT * FROM your_table WHERE your_condition;
END;
$$ LANGUAGE plpgsql;

调用后，即使事务提交，temp_result表依然存在，你可以随时查询其中的数据。

3. 升级到PostgreSQL 11+使用PROCEDURE #

如果条件允许，升级到PostgreSQL 11及以上版本后，可以使用PROCEDURE（存储过程）来实现，因为存储过程允许内部执行事务控制：

CREATE OR REPLACE PROCEDURE create_hold_cursor(OUT cur refcursor) AS $$
BEGIN
    -- 打开带WITH HOLD的游标
    OPEN cur WITH HOLD FOR SELECT * FROM your_table WHERE your_condition;
    COMMIT; -- 提交事务后游标被持有
END;
$$ LANGUAGE plpgsql;

调用方式：

CALL create_hold_cursor('my_hold_cursor');
-- 跨事务访问游标
FETCH NEXT FROM my_hold_cursor;

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