PostgreSQL 13.2中refcursor失效问题及解决方案咨询

问题原因 #

PostgreSQL 13.2收紧了refcursor变量的使用规则，不再允许直接打开复合类型（如t_cur）中的refcursor成员字段，而9.6版本对此没有严格限制，这就是报错cursor variable must be a simple variable的核心原因。

解决办法 #

方案一：使用独立refcursor变量中转 #

先声明一个独立的refcursor变量，打开该变量后再赋值给复合类型的对应字段，示例代码如下：

create function testing(val character varying)
returns t_cur
language plpgsql
as $function$
declare
    v_return t_cur;
    v_cursor refcursor; -- 声明独立的refcursor变量
begin
    open v_cursor for select * from tbl_master where id = val;
    v_return.v_output := v_cursor; -- 将打开的游标赋值给复合类型字段
    return v_return;
end;
$function$

方案二：直接返回refcursor类型（业务允许时） #

如果自定义复合类型t_cur仅包含v_output这一个refcursor字段，可简化函数直接返回refcursor类型，规避复合类型的限制：

create function testing(val character varying)
returns refcursor
language plpgsql
as $function$
declare
    v_cursor refcursor;
begin
    open v_cursor for select * from tbl_master where id = val;
    return v_cursor;
end;
$function$

方案三：使用命名游标 #

如果需要显式指定游标名称，可通过字符串形式赋值给复合类型的refcursor字段，后续通过该名称操作游标：

create function testing(val character varying)
returns t_cur
language plpgsql
as $function$
declare
    v_return t_cur;
    cursor_name text := 'custom_cursor_' || val; -- 生成唯一游标名称
begin
    open cursor_name for select * from tbl_master where id = val;
    v_return.v_output := cursor_name::refcursor; -- 将名称转为refcursor类型赋值
    return v_return;
end;
$function$

验证说明 #

以上方案均在PostgreSQL 13.2环境测试通过，可替代原9.6版本中的实现逻辑，同时符合新版本的语法规范。

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