Iterating and Dynamically Changing Elements of Type in PostgreSQL

Question

I've recently been asked to migrate a Database from Oracle to Postgres (I had no real choice in the matter). Using the ora2pg tool, I've managed to migrate most of the DDL, but the real headache started when I got my hands on some PL/SQL Code.

Oversimplifying things, here's some code which represents most of my problem (Similar problems appear in multiple parts of the code).

sql_statement := 'SELECT * FROM TABLE_1';

OPEN ref_cursor FOR sql_statement;
FETCH ref_cursor BULK COLLECT INTO list_a_aux;

WHILE list_a_aux.COUNT <> 0
LOOP
    FOR n in list_a_aux.FIRST..list_a_aux.LAST
    LOOP
        IF list_a_aux(n).id = 0 THEN
            list_a.EXTEND;
            list_a(list_a.COUNT).id = 1;
            list_a_aux.DELETE(n);
        ELSE
            -- More Application Logic
        END IF;
    END LOOP;
END LOOP;

Both list_a and list_a_aux are initiliazed as:

list_a      list_a:= list_a();
list_a_aux  list_a:= list_a();

Where list_a is a Type declared as:

TYPE list_a IS TABLE OF TABLE_2;

The first issue I ran into was the "BULK COLLECT" statement. I've searched some mailing lists, and I was pointed towards HERE. I understood the solution and it seemed simple enough, but then I delved deeper into the code I could not figure out how to integrate it with the remainder of the code. I've tried searching for problems similar to the ones present in this code, and I found solutions to many of the individual issues (1,2, etc.) but none seems to fit into this specific problem (not even when combined!).

Any ideias on how can I migrate this piece of PL/SQL?

Answer 1

Yes, there is no BULK COLLECT in PostgreSQL; you go about things in a different (maybe even easier) way.

As you have found out, you loop through query results like this:

FOR rec_var IN SELECT ... LOOP
   <statements>
END LOOP;

If you need collections of objects, you can use arrays – in your example you might use

DECLARE
   list_a table_2[] := ARRAY[]::table_2[];

to declare an array and initialize it to an empty array. There is a wealth of array functions and operators to manipulate them. You extend them simply by assigning to a new subscript or with array_append(). There is no function to remove the element at a certain index, but you can either create a new array by concatenating the slices before and after the element or you change your logic to use NULL for deleted elements.

Often, however, you can code things so that you don't need to wield collections of objects. You can use table functions (what would be PIPELINED functions in Oracle) to pass results around one at a time. Of course that is not always possible and might entail a substantial rewrite that you're probably not up for if your goal is to migrate existing code.