Functions with INOUT/OUT Parameters Conversion to PostgreSQL

Oracle allows you to create functions that would return a value, and at the same time have OUT and IN OUT parameters. Writing such functions, although it is bad practice, is a fairly common thing in Oracle databases, especially those written a long time ago. In our practice, we often encounter such functions, and we have trained our tool to accurately convert this type of function. Due to some features and limitations of PostgreSQL, converting such functions from Oracle to PostgreSQL is not the most trivial task. Our general approach for converting such functions from Oracle to PostgreSQL will be described below.

For example, we will use the following simple example of a function in 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;

The main problem with conventing such a function in PostgreSQL is that in PostgreSQL it is impossible to create a function that would return some value and, at the same time, have OUT (INOUT) parameters. If a function has such parameters, then it cannot return anything. Therefore, we convert such functions in the following way - we make this function return nothing and add an additional OUT parameter for the returned value. By default, the name of this parameter is SWP_RETVALUE, but you can change it if necessary. After converting this function with our tool, we get the following result:

 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; $$;

As you can see, the output is a function that returns nothing, preserves all types as parameters, and has an additional one - OUT SWP_RETVALUE DOUBLE PRECISION. Also, our tool will apply the corresponding changes for all calls to this function in other objects. For example, in some other object there will be the following call to this function:

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

As a result of the conversion from Oracle to PostgreSQL, this call will be replaced by the following function call:

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

As you can see, only IN and INOUT parameters will be passed to the function, and the result will be written to OUT and INOUT parameters, and in the right order. The result of the RES function will also be returned and stored. As you can see from this example, all function calls converted in this way will be converted absolutely correctly, and the user will not need to worry about the correct conversion of function calls in other objects. Also, this approach saves the type of the object (the function is converted to a function), which is also convenient for the user of our tool.

We follow this approach for all function types with INOUT(OUT) parameters. The only exceptions are functions, one of the parameters of which or the return value is a composite (record) type. In this case, our tool converts such functions into procedures, with an additional INOUT(OUT) parameter. This is due to the fact that in PostgreSQL it is impossible to return multiple parameters from a function if one of them is compound. Therefore, for such parameters, we convert such functions as procedures. Of cource, this solution will only work for PostgreSQL 11 and above. Calls to this function in other objects will also change accordingly.

This article describes the principles of converting functions with INOUT(OUT) parameters from ORacle to PostgreSQL. Due to the peculiarities of PostgreSQL, this is not the most trivial task, however, thanks to our tool, it can be completely solved without the need for manual adjustments.

If you have any questions or face any difficulties, please contact our support team: support@ispirer.com