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

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?

CodePudding user response：

PostgreSQL cursors do not support %ISOPEN or %NOTFOUND. To address this problem %ISOPEN can be replaced by a boolean variable declared internally in the procedure and is updated manually when the cursor is opened or closed.

http://wiki.openbravo.com/wiki/PL-SQL_code_rules_to_write_Oracle_and_Postgresql_code

CodePudding user response：

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).