Итерация и динамическое изменение элементов типа в PostgreSQL

Question

Меня недавно попросили перенести базу данных из Oracle в Postgres (у меня не было реального выбора в этом вопросе). Используя инструмент ora2pg, мне удалось перенести большую часть DDL, но настоящая головная боль началась, когда я взял в руки какой-то код PL/SQL.

Упрощение вещей, вот некоторый код, который представляет большую часть моей проблемы (Подобные проблемы появляются во многих частях кода).

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;

И list_a, и list_a_aux инициализируются как:

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

Где list_a - это тип, объявленный как:

TYPE list_a IS TABLE OF TABLE_2;

Первой проблемой, с которой я столкнулся, было утверждение "BULK COLLECT". Я искал несколько списков рассылки, и меня указали ЗДЕСЬ. Я понял решение, и оно показалось мне достаточно простым, но потом я углубился в код и не мог понять, как интегрировать его с остальной частью кода. Я попытался найти проблемы, аналогичные тем, которые присутствуют в этом коде, и я нашел решения многих отдельных проблем ( 1, 2 и т. Д.), Но ни одна из них, похоже, не вписывается в эту конкретную проблему (даже в сочетании!),

Любые идеи о том, как я могу перенести этот кусок PL/SQL?

Answer 1

Да нет BULK COLLECT в PostgreSQL; Вы идете по-другому (может быть, даже проще).

Как вы узнали, вы просматриваете результаты запроса следующим образом:

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

Если вам нужны коллекции объектов, вы можете использовать массивы - в вашем примере вы можете использовать

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

объявить массив и инициализировать его в пустой массив. Существует множество функций массива и операторов для управления ими. Вы расширяете их, просто назначая новый индекс или с помощью array_append(), Не существует функции для удаления элемента по определенному индексу, но вы можете либо создать новый массив, объединяя фрагменты до и после элемента, либо изменить свою логику для использования NULL для удаленных элементов.

Однако часто вы можете кодировать вещи так, чтобы вам не нужно было владеть коллекциями объектов. Вы можете использовать табличные функции (что было бы PIPELINED функции в Oracle) для передачи результатов по одному за раз. Конечно, это не всегда возможно и может повлечь за собой существенную переписку, на которую вы, вероятно, не рассчитываете, если ваша цель - перенести существующий код.