sql - 递归 PostgreSQL 函数失败并显示“游标已在使用”消息

Question

我有一个递归 PL/PgSQL 函数，它使用像这样的绑定(bind)参数化游标:

create or replace function test_cursor(rec boolean) returns void as $$
declare
    cur cursor(a int) for select * from generate_series(1,a);
begin
    for c in cur(3) loop
        if rec then
            perform test_cursor(false);
        end if;
    end loop;
end;
$$ language plpgsql;

函数递归调用自身时报错:

select test_cursor(true)

Code: 42P03, ErrorMessage: cursor "cur" already in use

显然我的游标范围不限于单个函数调用。在谷歌搜索解决方法后，我发现了这个 message在邮件列表文件中提到未绑定(bind)游标没有此限制，即:

declare
     mycursor refcursor;
begin
     open mycursor for ...;
end;

但我不知道如何参数化未绑定(bind)的游标。此外，我不能将 for...loop 与未绑定(bind)的游标一起使用:

-- 42601: cursor FOR loop must use a bound cursor variable
create or replace function test_cursor(rec boolean) returns void as $$
declare
    cur refcursor;
begin
    open cur for select * from generate_series(1,3);
    for c in cur loop
        if rec then
            perform test_cursor(false);
        end if;
    end loop;
    close cur;
end;
$$ language plpgsql;

有人可以建议替代方法吗？

附言。我正在移植大量大量使用递归和参数化游标的 Oracle 存储过程。在我用全局范围的游标遇到这个问题之前，转换似乎很简单。

Answer 1

我刚刚找到了一个变通方法，它对我来说很奇怪，但似乎仍然有效。我不确定它是否有任何缺点，但就我而言，唯一的选择是手动重写大量代码，所以我想我会尝试一下。

解决方案不是限制打开游标的范围，而是随机化它的公共(public)可见名称(门户名称)，这样每次我重新进入我的函数时，我都会得到一个新的门户名称:

create or replace function test_cursor(rec boolean default true) returns void as $$
declare
    cur cursor(a int) for select * from generate_series(1,a);
begin
    -- assign a random string as a portal name
    -- before iterating over the cursor
    cur := random_portal_name();

    for c in cur(3) loop
        if rec then
            perform test_cursor(false);
        end if;
    end loop;
end;
$$ language plpgsql;

有很多方法可以获取随机字符串:从序列中获取下一个值，生成 UUID，等等。我为自己编写了一个辅助函数，它为此目的创建了一个临时( session 范围)序列:

create or replace function random_portal_name() returns varchar as $$
begin
    create temp sequence if not exists portal_names;
    return 'portal$' || nextval('portal_names');
end;
$$ language plpgsql;