Конвертация табличных типов из Oracle в PostgreSQL с помощью массивов

Oracle предлагает удобный механизм для работы с табличными типами данных. К сожалению, в других базах данных это может стать проблемой. PostgreSQL, например, не поддерживает табличные типы, поэтому вопрос о том, как именно преобразовывать такие типы, очень важен.

Существует несколько способов преобразования табличных типов данных в PostgreSQL. Первый — преобразование с помощью временных таблиц. Это решение довольно простое, но у него есть один большой недостаток — оно сильно загромождает код и делает его очень сложным для чтения. Ведь простые операции будут заменены запросами из временной таблицы.

Второй вариант — преобразование табличных типов с помощью массивов в PostgreSQL.

В этой статье описаны основные возможности и реализация преобразования табличных типов с помощью массивов в PostgreSQL. Чтобы лучше понять принципы преобразования, давайте преобразуем следующий пример:

 CREATE TYPE employee AS OBJECT (
    id NUMBER,
    Name VARCHAR(300)
    );
 CREATE TYPE employees_tab IS TABLE OF employee;

 CREATE OR REPLACE PROCEDURE hire(EMPLOYEES in out employees_tab, id NUMBER, Name VARCHAR) AS
 NEW_EMPLOYEES employees_tab := employees_tab();
 BEGIN
    EMPLOYEES.Extend(1);
    EMPLOYEES(EMPLOYEES.count) := employee(id, Name);
    FOR i IN EMPLOYEES.first..EMPLOYEES.last
    LOOP
       INSERT INTO emp_tab values (EMPLOYEES(i).id, EMPLOYEES(i).Name);
    END LOOP;
    INSERT INTO EMP_TAB SELECT * FROM TABLE(EMPLOYEES);
    NEW_EMPLOYEES.Extend(EMPLOYEES.count);
    NEW_EMPLOYEES.Delete;
 END;

Ниже приведен пример преобразования этой процедуры с использованием массивов в PostgreSQL:

 CREATE TYPE EMPLOYEE AS(id DOUBLE PRECISION,Name VARCHAR(300));
 -- CREATE TYPE EMPLOYEES_TAB IS TABLE OF employee;
 CREATE OR REPLACE PROCEDURE HIRE(INOUT EMPLOYEES EMPLOYEE[] ,
 ID DOUBLE PRECISION, NAME VARCHAR(4000))
 LANGUAGE plpgsql
    AS $$
    DECLARE
    EMPLOYEES_REC  EMPLOYEE;
    NEW_EMPLOYEES  EMPLOYEE[] default array[]::EMPLOYEE[] ;
 BEGIN
    EMPLOYEES[swf_array_length(EMPLOYEES)+1] := null;
 EMPLOYEES[swf_array_length(EMPLOYEES)] := row(ID,NAME);
    FOR i IN array_lower(EMPLOYEES,1) .. array_upper(EMPLOYEES,1)
    LOOP
       EMPLOYEES_REC := EMPLOYEES[i];
       INSERT INTO EMP_TAB  values(EMPLOYEES_REC.ID, EMPLOYEES_REC.NAME);
       EMPLOYEES[i] := EMPLOYEES_REC;
    END LOOP;
    INSERT INTO EMP_TAB  SELECT * FROM unnest(EMPLOYEES);
    for i in 1 .. swf_array_length(EMPLOYEES) loop
       NEW_EMPLOYEES[swf_array_length(NEW_EMPLOYEES)+1] := null;
    end loop;
    NEW_EMPLOYEES := array[]:: EMPLOYEE[];
 END; $$;

Чтобы лучше понять механизм преобразования, давайте рассмотрим преобразование каждого метода из примера по отдельности:

Преобразование типов данных таблицы

Сами табличные типы данных не преобразуются. Вместо них в коде будут использоваться массивы того типа, из которого состоит данный тип таблицы:

Объявление и инициализация переменных табличного типа

Вместо того чтобы инициализировать переменную в Oracle, в PostgreSQL мы заполняем ее пустым массивом заданного типа:

Конвертация метода Count

Для преобразования метода Count была разработана дополнительная функция swf_array_length, которая будет автоматически сгенерирована и создана в базе данных PostgreSQL в процессе преобразования.

Расширение преобразования методов

Вместо метода Extend мы просто добавляем пустой элемент в конец массива.

Преобразование методов Last и First

Для преобразования a.first и a.last используйте array_lower(a ,1) и array_upper(a,1) соответственно.

Инициализация элемента TYPE IS TABLE

Чтобы инициализировать элемент массива, мы используем конструктор row().

Преобразование метода удаления

Вместо того, чтобы полностью удалять все строки из переменной, мы просто заполняем переменную пустым массивом в PostgreSQL.

Преобразование функции Table()

Вместо функции TABLE, которая позволяет нам ссылаться на табличную переменную как на таблицу, в PostgreSQL используется функция UNNEST. Вместо псевдоколонки column_value — unnest в PostgreSQL.

Доступ полей к составному элементу TYPE IS TABLE

Oracle позволяет ссылаться на поле составного элемента массива по номеру элемента (EMPLOYEES( i ).id). Это невозможно сделать для PostgreSQL версии 13 и ниже. В этом случае для доступа к полю массива необходимо сначала поместить этот элемент в новую переменную, а затем обратиться к его полю:

Для версии PostgreSQL 14 и выше разрешен доступ к элементу по его индексу:

Это решение не только позволяет эффективно конвертировать табличные типы, но и делает код гораздо более точным по сравнению с конвертацией с использованием временных таблиц. Также этот механизм можно использовать при преобразовании типов VARRAYS.

Разработчики, знакомые с работой табличных типов в Oracle и типов массивов в PostgreSQL, могли заметить, что между ними есть одно существенное различие — переменные табличного типа могут иметь пустые элементы в середине (пустые строки), в то время как в PostgreSQL все элементы массива должны идти по порядку. Этой проблемы можно избежать небольшим изменением логики в SQL-коде.
С помощью этого решения можно конвертировать не только такие простые случаи, но и более сложные, когда к элементу обращаются в массиве массивов и т. д.

Наш инструмент поддерживает преобразование табличных типов как с временными таблицами, так и с массивами (по умолчанию). Для того чтобы использовать решение с массивами, воспользуйтесь параметром TABLE_TYPE_CONVERSION=Arrays в разделе [POSTGRE] ini-файла (как установить параметры в Конвертум Мастере). В этом случае вместо типов будут использоваться массивы, а типы не будут создаваться в PostgreSQL. По умолчанию для этого параметра используется значение "Arrays". Пустые значения равны значению "Arrays". Если параметр имеет значение "Tables", то будут использоваться временные таблицы.