Question 114444 in PostgreSQL Question posted in
The official documentation can be found here.

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

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?

Tags:

3

Answers


  1. 0

    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

    Login or Signup to reply.
  2. 0

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

    Login or Signup to reply.
  3. 0

    I often found it convenient in cases like this to create a function that emulates Oracle. In his case something like:

    create or replace function cursor_isopen(cur text)  
    returns boolean 
   language sql
as $$
   select exists (select null 
                    from pg_cursors 
                   where name = cur
                 ) ;
$$;

    Then your code becomes something like:

    exception
    when others then
        if cursor_isopen(cur_name::text) then
            close SPV_RECON_INFO;
        end if;

    Of course you need to have preset the cursor name as Laurenz Albe has pointed out. Sample test case.

    do $$ 
declare
   cur1 cursor for select table_name from information_schema.tables; 
   cur2 cursor for select table_name from information_schema.tables; 
   table_name text; 
begin
   cur1 := 'closed-cursor';
   cur2 := 'open-cursor';   
   open cur2; 
   
   if cursor_isopen(cur1::text) 
       then 
            fetch cur1 into table_name; 
            raise notice 'First table name: %', table_name;
            close cur1;
       else raise notice 'cursor_isopen(''%'') returned %', cur1::text, cursor_isopen(cur1::text);
   end if; 
 
   if cursor_isopen(cur2::text) 
       then 
            fetch cur2 into table_name; 
            raise notice 'First table name: %', table_name;
            close cur2;
       else raise notice 'cursor_isopen(''%'') returned %', cur1::text, cursor_isopen(cur1::text);
   end if;
   
end;
$$;

    results:
    cursor_isopen(‘closed-cursor’) returned f
    cursor_isopen(‘open-cursor’) returned t. First table name: task_states

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search