how do I test if a refcursor is already open in plpgsql

Question

I am trying to migrate Oracle Procedure / Funcnctions in Postgres in which I am finding few difficulties dealing with cursor syntax in PostgreSQL. Oracle proc snippet is:

IF v_user_auth THEN
              OPEN no_emp_cur;
            ELSE
              OPEN un_auth_no_emp_cur;
            END IF;
          ELSE
            IF v_direction = 'newer' THEN
              IF v_user_auth THEN
                OPEN newer_cur;
              ELSE
                OPEN un_auth_new_cur;
              END IF;
            ELSE -- older
              IF v_user_auth THEN
                OPEN older_cur;
              ELSE
                OPEN un_auth_old_cur;
              END IF;
            END IF;
          END IF;

          LOOP
            IF no_emp_cur%ISOPEN THEN
              FETCH no_emp_cur INTO v_emp_rec;
              IF NOT FOUND THEN EXIT; END IF; -- apply on no_emp_cur
            ELSIF un_auth_no_emp_cur%ISOPEN THEN
              FETCH un_auth_no_emp_cur INTO v_emp_rec;
              IF NOT FOUND THEN EXIT; END IF; -- apply on un_auth_no_emp_cur
            ELSIF newer_cur%ISOPEN THEN
              FETCH newer_cur INTO v_emp_rec;
              IF NOT FOUND THEN EXIT; END IF; -- apply on newer_cur
            ELSIF older_cur%ISOPEN THEN
              FETCH older_cur INTO v_emp_rec;
              IF NOT FOUND THEN EXIT; END IF; -- apply on older_cur
            ELSIF un_auth_new_cur%ISOPEN THEN
              FETCH un_auth_new_cur INTO v_emp_rec;
              IF NOT FOUND THEN EXIT; END IF; -- apply on un_auth_new_cur
            ELSIF un_auth_old_cur%ISOPEN THEN
              FETCH un_auth_old_cur INTO v_emp_rec;
              IF NOT FOUND THEN EXIT; END IF; -- apply on un_auth_old_cur
            END IF;

            Few Processing ...

            END LOOP;
          IF no_emp_cur%ISOPEN THEN
            CLOSE no_emp_cur;
          ELSIF un_auth_no_emp_cur%ISOPEN THEN
            CLOSE un_auth_no_emp_cur;
          ELSIF newer_cur%ISOPEN THEN
            CLOSE newer_cur;
          ELSIF older_cur%ISOPEN THEN
            CLOSE older_cur;
          ELSIF un_auth_new_cur%ISOPEN THEN
            CLOSE un_auth_new_cur;
          ELSIF un_auth_old_cur%ISOPEN THEN
            CLOSE un_auth_old_cur;
          END IF;
    END IF;

Please help with the syntax to migrate this in postgres.

Thanks in advance!

Answer 1

Use an unbound cursor variable.

DECLARE
   c refcursor;
BEGIN
   IF ... THEN
      OPEN c FOR SELECT ...;
   ELSE
      OPEN c FOR SELECT ...;
   END IF;

   LOOP
      FETCH c INTO v_emp_rec;
      ...
   END LOOP;

   CLOSE c;
END;