Конвертация BULK COLLECT в PostgreSQL

BULK COLLECT — это метод получения данных, во время которого в коллекцию одновременно помещается множество строк. Проблематичной может стать конвертация процедур из баз данных, поддерживающих этот процесс, например, Oracle, в системы, которые его не поддерживают, например, PostgreSQL. Инструмент Конвертум может решить эту проблему и преобразовать процедуры, содержащие BULK COLLECT, несколькими способами. Рассмотрим на примере ковнвертации процедур из Oracle в PostgreSQL.

Инструмент Конвертум преобразует переменные типа collection либо во временные таблицы, либо в массив. Это зависит от параметра TABLE_TYPE_CONVERSION в разделе [POSTGRE] ini-файла (как установить параметры в Конвертум Мастере):

TABLE_TYPE_CONVERSION может иметь следующие значения:

• TABLE_TYPE_CONVERSION=Tables
• TABLE_TYPE_CONVERSION=Arrays

По умолчанию используется значение "Arrays". Оно используется, когда параметр отсутствует. Если установлено значение "Arrays", то переменные типа collection преобразуются в массивы, в противном случае — во временные таблицы. Конвертация таких переменных в массивы удобнее для дальнейшей работы с ними. Подробнее об этом читайте в статье "Конвертация табличных типов с помощью массивов".

В случае, когда переменная типа коллекция становится временной таблицей, BULK COLLECT преобразуется в простую вставку в созданную таблицу.
Код Oracle:

 CREATE TYPE type_bulk AS object (p1 integer, p2 varchar2(22)); 
 CREATE TYPE type_bulk_tab AS TABLE OF type_bulk; 
 CREATE FUNCTION func_for_bulk(par IN NUMBER) 
 RETURN type_bulk_tab AS RESULTS type_bulk_tab; 
 BEGIN 
     SELECT type_bulk(p1,p2) 
     BULK COLLECT INTO RESULTS 
     FROM (SELECT c1 as p1, c2 as p2 from table_for_bulk);
     RETURN RESULTS; 
 END;

После преобразования с помощью нашего инструмента мы получим следующий результат в PostgreSQL:

 CREATE FUNCTION FUNC_FOR_BULK (IN PAR DOUBLE PRECISION) 
 RETURNS TABLE ( SWC_Index INTEGER, p1 INTEGER, p2 VARCHAR(22) ) 
 LANGUAGE plpgsql AS $$ 
 BEGIN 
     create temporary table if not exists SWT_FUNC_FOR_BULK_RESULTS ( SWC_INDEX INTEGER NOT NULL, P1 INTEGER, P2 
 VARCHAR(22) ); 
     DELETE FROM SWT_FUNC_FOR_BULK_RESULTS; 
     INSERT INTO SWT_FUNC_FOR_BULK_RESULTS SELECT row_number() OVER(), TabAl.P1,TabAl.P2 from(SELECT C1 as P1,C2 
 as P2 from TABLE_FOR_BULK) AS TABAL; 
     RETURN QUERY(SELECT SWT_FUNC_FOR_BULK_RESULTS.SWC_INDEX, SWT_FUNC_FOR_BULK_RESULTS.P1, 
 SWT_FUNC_FOR_BULK_RESULTS.P2 FROM SWT_FUNC_FOR_BULK_RESULTS); 
 END; $$;

Если для BULK COLLECTION данных используется несколько переменных, для каждой из них создаются временные таблицы.
Код Oracle:

 SELECT id, full_name 
 BULK COLLECT INTO v_id, v_full_name 
 FROM TABLE_FOR_BULK;

Код PostgreSQL:

 create temporary table if not exists SWT_PRC_BULK_COLLECT_V_FULL_NAME ( SWC_Index INTEGER NOT NULL, SWC_Value 
 VARCHAR(30) ); 
 DELETE FROM SWT_PRC_BULK_COLLECT_V_FULL_NAME; 
 create temporary table if not exists SWT_PRC_BULK_COLLECT_V_ID ( SWC_Index INTEGER NOT NULL, SWC_Value DOUBLE 
 PRECISION ); 
 DELETE FROM SWT_PRC_BULK_COLLECT_V_ID; 

 INSERT INTO SWT_PRC_BULK_COLLECT_V_ID SELECT row_number() OVER(), id FROM TABLE_FOR_BULK; 
 INSERT INTO SWT_PRC_BULK_COLLECT_V_FULL_NAME SELECT row_number() OVER(), full_name FROM TABLE_FOR_BULK;

Если переменная типа varray_type преобразуется в массив с помощью опции TABLE_TYPE_CONVERSION=Arrays, вместо BULK COLLECTION создается новый массив, который затем присваивается переменной типа array. Код Oracle:

 CREATE FUNCTION func_with_varray() 
 RETURN varray_type IS 
     Arr varray_type := varray_type(); 
 BEGIN 
     SELECT name 
     BULK COLLECT INTO Arr 
     FROM TABLE_FOR_BULK; 
     RETURN Arr; 
 END;

Код PostgreSQL:

 CREATE OR REPLACE FUNCTION FUNC_WITH_VARRAY() 
 RETURNS VARCHAR(60)[] 
 LANGUAGE plpgsql AS $$ 
 DECLARE 
     ARR VARCHAR(60)[] default array[]::VARCHAR(60)[]; 
 BEGIN 
     ARR := ARRAY(SELECT NAME FROM TABLE_FOR_BULK); 
     RETURN ARR; 
 END; $$;

В результате программное обеспечение Конвертум корректно конвертирует BULK COLLECT из Oracle в PostgreSQL.