Конвертация в PostgreSQL функции с параметрами INOUT/OUT

Oracle позволяет создавать функции, которые возвращают значение и при этом имеют параметры OUT и INOUT. Написание таких функций, хотя и является плохой практикой, довольно часто встречается в базах данных Oracle, особенно в очень старых. В нашей практике мы часто сталкиваемся с такими функциями, и мы обучили наш инструмент преобразованию такого типа функций. В силу некоторых особенностей и ограничений PostgreSQL, конвертация таких функций из Oracle в PostgreSQL — не самая тривиальная задача. Ниже будет описан наш общий подход к преобразованию таких функций из Oracle в PostgreSQL.

Для примера мы используем простую функции в Oracle:

create or replace function fun (a in number,b out number, c in out number) return number is
 begin
   b:=a+10;
   c:=c+a;
   return(50);
 end;

Основная проблема заключается в том, что в PostgreSQL невозможно создать функцию, которая возвращала бы значение и в то же время имела бы параметры OUT (INOUT). Если у функции есть такие параметры, то она не может ничего вернуть. Поэтому мы преобразуем такие функции следующим образом — принуждаем функцию ничего не возвращать и добавляем дополнительный OUT-параметр для возвращаемого значения. По умолчанию этот параметр называется SWP_RETVALUE, но при необходимости вы можете его изменить. После преобразования этой функции с помощью нашего инструмента мы получим следующий результат:

 CREATE OR REPLACE FUNCTION FUN(in A DOUBLE PRECISION ,OUT B DOUBLE PRECISION , INOUT C DOUBLE PRECISION , OUT 
 SWP_RETVALUE DOUBLE PRECISION)
 LANGUAGE plpgsql
    AS $$
 BEGIN
    B := A+10;
    C := C+A;
    SWP_RETVALUE :=(50);
    RETURN;
 END; $$;

На выходе получается функция, которая ничего не возвращает, сохраняет все типы в качестве параметров и получает дополнительный — OUT SWP_RETVALUE DOUBLE PRECISION. Также наш инструмент применяет соответствующие изменения для всех вызовов этой функции в других объектах. Например, в каком-то другом объекте будет следующий вызов этой функции:

 RES:=FUN(X,Y,Z);

В результате перехода с Oracle на PostgreSQL этот вызов будет заменен следующим вызовом функции:

 SELECT * FROM FUN(X,Z) INTO STRICT Y,Z,RES;

Как видите, в функцию будут переданы только параметры IN и INOUT, а результат будет записан в параметры OUT и INOUT, причем в правильном порядке. Результат функции RES также будет возвращен и сохранен. Как видно в примере, все вызовы функций безошибочно сконвертированы. Кроме того, при таком подходе сохраняется тип объекта (функция преобразуется в функцию), что также удобно для пользователя нашего инструмента.

Мы используем этот подход для всех типов функций с параметрами INOUT (OUT). Исключение составляют функции, один из параметров которых или возвращаемое значение является составным (рекордным) типом. В этом случае наш инструмент преобразует такие функции в процедуры, с дополнительным параметром INOUT (OUT). Это связано с тем, что в PostgreSQL невозможно вернуть несколько параметров из функции, если один из них составной. Поэтому для таких параметров мы преобразуем функции в процедуры. Разумеется, это решение будет работать только для PostgreSQL 11 и выше. Соответственно изменятся и вызовы этих функций в других объектах.