PL/pgSQL Conversion Gotchas: Cursors and Last Fetch.

Databases Cursors are one of the key components of development. We have been using them for traversing query results for processing based on business logic or functionality. In Code Conversion, Cursor code always needs to be ported either from PL\SQL or TSQL to Pl\pgSQL, and some special considerations need to be incorporated as best practices to avoid surprises or functionality failures.

If you are keen to understand cursors in Pl\pgSQL, will advice do checkout PostgreSQL official documents it covers most of the details in depth.

In the blog, we will cover scenarios with cursors that differ from how Oracle handles them. During conversion, our initial approach is to match all codebases as closely as possible with the target compatibility. However, in some cases, although the code appears identical, the functionality might vary. Let’s explore one such case here.

Let’s start with snippet of code in Oracle PLSQL.

set serveroutput on;

DECLARE
  CURSOR c1 IS
    SELECT level as col1 ,  level+1 as col2 
    from dual connect by level <=10;
    v_id1 integer;
    v_id2 integer;
  BEGIN
    OPEN c1;
    LOOP 
      FETCH c1 INTO v_id1,v_id2;
      EXIT WHEN c1%NOTFOUND;
    END LOOP;
  CLOSE c1;
  DBMS_OUTPUT.PUT_LINE('Last Value of v_id1 - ' || v_id1);
  DBMS_OUTPUT.PUT_LINE('Last Value of v_id2 - ' || v_id2);
END;
/

After running the PLSQL block, the values for both variables will be as per the last fetch. Now, with a different requirement, we would be referring to the last fetch values and performing further processing in the code logic. It’s important to understand that we exited the loop due to NOTFOUND in Oracle, but the last value is still as per the last FOUND.

Now, let’s examine the PostgreSQL-compatible code for the same in PL\pgsql. The PostgreSQL-compatible code appears as follows; most of the codebase looks similar due to compatibility with PLSQL.

DO $$
DECLARE
  c1 CURSOR FOR SELECT col1, col1+1 as col2 
  from generate_series(1,10) col1;
  v_id1 integer;
  v_id2 integer;
BEGIN
 OPEN c1;
  LOOP
    FETCH FROM c1 INTO v_id1,v_id2;
    EXIT WHEN NOT FOUND;
  END LOOP;
  CLOSE c1;
  raise notice 'Last Value of v_id1 - %', v_id1;
  raise notice 'Last Value of v_id2 - %', v_id2;
END$$;

Let’s run the same code and check the output.

One key difference that you can make out from the output is, in Pl\pgSQL last values is overwrite for NOT FOUND case and it would not be same functionally as it would have work in Oracle.

In PostgreSQL, FETCH behaves similarly to SELECT INTO, overwriting the last fetch that doesn’t return any rows for traversal with NULL. Therefore, when we output the last values after the cursor loop, they were NULL, and the loop exited due to NOT FOUND.

In code conversion, one of our key success criteria is to match functionality and achieve the same output as the input. Despite the code conversion appearing similar in the cases mentioned above, we might encounter failed test cases based on functionality differences.

Now, lets check for some workaround.

1. Use Temporary record or variables to hold values from Cursors.
2. Use For Loop way to iterate rows with implicit cursor handling.

Use Temporary record or variables to hold values from Cursors.

DO $$
DECLARE
  c1 CURSOR FOR SELECT col1, col1+1 as col2 
                from generate_series(1,10) col1;
  temp record;
  v_id1 integer;
  v_id2 integer;
BEGIN
 OPEN c1;
  LOOP
    FETCH FROM c1 INTO temp;
    EXIT WHEN NOT FOUND;
    v_id1 := temp.col1;
    v_id2 := temp.col2;
  END LOOP;
  CLOSE c1;
  raise notice 'Last Value of v_id1 - %', v_id1;
  raise notice 'Last Value of v_id2 - %', v_id2;
END$$;

Use For Loop way to iterate rows with implicit cursor handling.

DO $$
DECLARE
  temp record;
  v_id1 integer;
  v_id2 integer;
BEGIN
 FOR temp in (SELECT col1, col1+1 as col2 
       from generate_series(1,10) col1)
  LOOP
    v_id1 := temp.col1;
    v_id2 := temp.col2;
  END LOOP;

  raise notice 'Last Value of v_id1 - %', v_id1;
  raise notice 'Last Value of v_id2 - %', v_id2;
END$$;

Conclusion

Most of the cursor components in PostgreSQL are compatible with Oracle, but we need to be aware of how the last fetch is handled and how it can overwrite function variables with NULL. We need to handle this with the suggested workaround.

1. Use Temporary record or variables to hold values from Cursors.
2. Use For Loop way to iterate rows with implicit cursor handling.

If you really enjoyed the blog and are exploring courses to master PL/pgSQL development on PostgreSQL, please check out my Live courses.