What is the equivalent of PL/SQL %ISOPEN in PL/pgSQL?

Question

I'm migrating an Oracle PLSQL SP to be compatible with Postgres plpgsql (version PostgreSQL 13.6 on x86_64-pc-linux-gnu, compiled by x86_64-pc-linux-gnu-gcc (GCC) 7.4.0, 64-bit).

The exception block of the PLSQL SP has the below code:

exception
    when others then
        if CURR1%isopen then
            close SPV_RECON_INFO;
        end if;
        open CURR1 for execute select sysdate from dual;
END;

How can %isopen be implemented in Postgres?

Answer 1

That is simple. You have to assign a name to the cursor variable, then you can search for that cursor in pg_cursors. If there is a row with that name, the cursor is open.

Here is a self-contained example:

DO
$$DECLARE
   c refcursor;
BEGIN
   c := 'mycursor';
   /* cursor is not open, EXIST returns FALSE */
   RAISE NOTICE '%', EXISTS (SELECT 1 FROM pg_cursors WHERE name = 'mycursor');
   OPEN c FOR SELECT * FROM pg_class;
   /* cursor is open, EXIST returns TRUE */
   RAISE NOTICE '%', EXISTS (SELECT 1 FROM pg_cursors WHERE name = 'mycursor');
END;$$;

NOTICE:  f
NOTICE:  t

If you do not assign a name, PostgreSQL will generate a name (but you don't know what the name is).