Recursive PostgreSQL function fails with a «cursor already in use» message

Question

I have a recursive PL/PgSQL function that uses a bound parameterized cursor like this:

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;

When the function calls itself recursively, it reports an error:

select test_cursor(true)

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

Apparently the scope of my cursor isn't limited to a single function call. After googling for a workaround I found this message in the mailing list archives which mentions that unbound cursors don't have this limitation, i.e:

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

But I don't see how I can parameterize an unbound cursor. Also, I cannot use for...loop with an unbound cursor:

-- 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;

Could someone suggest an alternative approach?

PS. I'm porting a large amount of Oracle stored procedures which heavily use recursion and parameterized cursors. The conversion seemed straightforward until I hit this problem with globally scoped cursors.

Answer 1

I've just found a workaround which looks pretty weird to me but still seems to work. I'm not sure if it has any drawbacks, but in my case the only alternative is to rewrite quite a lot of code manually, so I guess I'll try it out.

The solution is not to restrict the scope of the open cursor, but to randomize its public-visible name (portal name) so that every time I re-enter my function I get a fresh portal name:

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;

There are a bunch of ways to get a random string: get the next value from a sequence, generate a UUID, to name a few. I wrote myself a helper function that creates a temporary (session-scoped) sequence for that purpose:

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;