When migrating Oracle routines to PostgreSQL, handling OUT and INOUT parameters can be tricky. Understanding the distinctions between Oracle and PostgreSQL in how they manage these parameters is essential for a successful migration. This knowledge helps you smoothly adjust your routines, ensuring your code works well without any issues. In this article, we shall explore IN, OUT and INOUT parameters in Oracle and PostgreSQL and understand some of the important differences.
Pass by value vs Pass by reference in databases
We are already familiar with the terms "pass by value" and "pass by reference". Passing a copy of the value is referred to as "passing by value." The term "passing by reference" refers to passing the variable’s real memory reference.
The methods used by Oracle and PostgreSQL to pass and process routine parameters vary. We must comprehend the internals and distinctions between Oracle and PostgreSQL in order to write or migrate code effectively.
A simple example in Oracle
Let us first create the below simple procedure.
CREATE OR REPLACE PROCEDURE test_pr(p_a OUT number) IS
BEGIN
p_a := 3;
END;
Output:
Procedure created.
Now we will call the above procedure from an anonymous block.
DECLARE
v_a number := 5;
BEGIN
test_pr(v_a);
DBMS_OUTPUT.PUT_LINE('The value of v_a is: ' || v_a);
END;
Output:
The value of v_a is: 3
In this example, we declared a variable ‘v_a’ in the anonymous block and passed it to the test_pr procedure as an OUT parameter, which assigns the value of ‘p_a’ to 3. As a result, when we display the value of ‘v_a’ after calling the test_pr procedure, the result is 3. Now let us see the internals of this.
Oracle internals of passing IN, OUT, IN OUT parameters
By default, Oracle passes OUT and IN OUT parameters by value and IN parameters by reference. Yes, you read it right! When an OUT or IN OUT parameter is changed within a procedure, it only changes a copy of the parameter value. The result value is copied back to the formal parameter only after the procedure has completed without errors.
If you send a collection as an OUT or IN OUT parameter, it will be passed by value. That means the full collection will be copied from the formal parameter to actual parameter when entering the procedure and copied back to formal parameter when exiting it. If the collection is huge, this can consume a lot of CPU and Memory. The NOCOPY parameter mode hint solves this problem by instructing the runtime engine to try passing OUT or IN OUT arguments by reference rather than by value.
PostgreSQL’s way of handling IN, OUT, INOUT parameters
PostgreSQL does not support passing parameters by reference; instead, it only supports pass-by-value. There is no reference passed when using an OUT or INOUT parameter, but the returned values are taken from the result composite.
What happens when a PostgreSQL procedure with OUT parameters is called from plain SQL?
In plain SQL, if the procedure has output parameters, it returns the final values of the output parameter variables to the caller when called with CALL statement.
Consider below procedure as an example.
CREATE OR REPLACE PROCEDURE add_numbers(
IN a INT,
IN b INT,
OUT result INT
)
LANGUAGE plpgsql
AS $$
BEGIN
result := a + b;
END;
$$;
Now if we call this procedure in plain SQL as below
postgres=# CALL add_numbers(3, 5, 0);
Output:
result
------
8
Here I am just passing zero for the OUT parameter. We observe that values of OUT parameters are directly returned as a composite to the caller.
What happens when a PostgreSQL procedure with OUT parameters is called from a function, procedure, or DO block?
When a PL/pgSQL function, procedure, or DO block uses CALL to execute a procedure, the output parameters are handled differently than when using CALL in plain SQL. And, each of it’s procedure’s OUT or INOUT parameters must be provided with a variable in the CALL statement, and whatever the procedure returns is assigned back to that variable. This is implicitly done by the CALL statement. To learn more refer here.
Consider below DO block which calls the above add_numbers procedure.
DO $$
DECLARE
result_value INT := 0;
BEGIN
CALL add_numbers(3, 5, result_value);
RAISE NOTICE 'The result is: %', result_value;
END $$;
Output:
NOTICE: The result is: 8
DO
We observe that the result printed is 8 as CALL here is handling that.
SELECT and PERFORM in PL/pgSQL
The SELECT and PERFORM statements in PL/pgSQL, when used to execute a function with an OUT or INOUT argument, do not handle output parameters after the results are returned. Therefore the results are not assigned back to the variables we used in the function call, unlike the PL/pgSQL CALL command. In order to allocate the results to the variables, we utilize the SELECT.. INTO statement.
The SELECT command is used to invoke both PL/pgSQL and plain SQL functions. However, SELECT queries that don’t have an INTO clause are not allowed in PL/pgSQL. Instead PERFORM is used to call a void function, the function without any output parameters.
Do PostgreSQL functions support OUT/INOUT parameters and a RETURN value at once?
This curious situation arises when we have one or more OUT/INOUT parameters and something is being returned from a PostgreSQL function at the same time.
Consider the following Oracle function.
CREATE FUNCTION test_ro(x number, y OUT number)
RETURN boolean IS
BEGIN
y := x;
RETURN true;
END;
And its equivalent PostgreSQL function
CREATE OR REPLACE FUNCTION test_ro(x bigint, y OUT bigint)
RETURNS boolean AS $$
BEGIN
y := x;
RETURN true;
END; $$ LANGUAGE plpgsql
However, this function cannot be created in PostgreSQL. An error pops up saying
ERROR: function result type must be bigint because of OUT parameters
SQL state: 42P13
The PostgreSQL function given above has OUT/INOUT arguments and returns a Boolean value. These kind of functions are not valid in PostgreSQL. In PostgreSQL, the values of the OUT/INOUT parameters, and only these values, specify the outcome of a function with OUT/INOUT parameters.
Although PostgreSQL and Oracle have similar OUT/INOUT parameter syntax, their implementations differ significantly. In PostgreSQL, all parameters are passed by value only because PostgreSQL does not support passing parameters by reference.
When a PostgreSQL function have OUT/INOUT arguments, there is no room for returning any further data via RETURN clause. Because the outcome composite is constructed exclusively from the OUT/INOUT parameters in this case. This is the reason the return value is not possible here.
The RETURNS clause is optional if there are OUT or INOUT parameters. It must match the result type specified by the OUT/INOUT parameters if they are present. If there are several OUT/INOUT parameters then RECORD should be used. If there is only one OUT/INOUT parameter, the return type must match that single OUT/INOUT parameter.
So, the above PostgreSQL function is incorrect because the actual output of the test_ro function is a scalar bigint value, which is contrary to the specified boolean. RETURN true is incorrect as well because the result is based solely on the OUT/INOUT parameters, and RETURN should be empty.
What would then be the proper migration strategy for such Oracle functions?
In this situation where we have a return statement returning the variable and OUT/INOUT arguments, we need to make the returning variable as OUT parameter. This leaves the function with just RETURN statement returning nothing. Also, the return type of the function must be changed to RECORD instead of the previous returned datatype (boolean) as we are now returning two OUT parameters.
So the equivalent translation of function test_ro from Oracle to Postgres would be the following.
CREATE OR REPLACE FUNCTION test_ro(x bigint, y OUT bigint, OUT result boolean)
RETURNS record AS $$
BEGIN
y := x;
result := true;
RETURN;
END; $$ LANGUAGE plpgsql
When the function return type is RECORD, the function is expected to be called in FROM clause. Variables that corresponds to the OUT parameters in function call can be omitted. A sample call to the above function would be
DO $$
DECLARE
a bigint := 5;
b boolean := false;
BEGIN
SELECT * INTO a, b FROM test_ro(10);
RAISE NOTICE 'a: %, b: %', a, b;
END;
In Postgres, when a function has OUT/INOUT variables, the RETURN statement is only used to end the execution and not to specify the returning value. The return value is determined by value assigned to the OUT/INOUT parameters.
Conclusion:
-
Oracle passes IN parameters by reference and OUT and IN OUT parameters by value. Pass by reference is not supported by PostgreSQL; instead, all parameters are passed via the pass by value.
-
In PostgreSQL, the CALL statement behaves differently in PL/pgSQL and plain SQL. In PL/pgSQL, the CALL statement automatically assigns the values returned from the procedure via the OUT/INOUT arguments to the variables that are specified in the CALL statement. With functions, we use SELECT.. INTO to manually assign OUT/INOUT parameter results to variables.
-
As PostgreSQL only supports pass by value, and only one entity should be output from the function, functions with both OUT/INOUT arguments and return variable are not supported. In this case that single return entity from function is only specified by OUT/INOUT parameters value. Only the values of the OUT/INOUT arguments is used to provide the single return entity from the function. The RETURNS clause here is optional, and the RETURN statement is just used to terminate execution—it does not specify the returned value.
-
If we want to convert Oracle function with both return variable and OUT/INOUT parameters to PostgreSQL then we need to make the return variable too an OUT parameter in PostgreSQL and change the return datatype to RECORD or remove RETURNS clause completely. Hopefully Ora2Pg can do that automatically for you.
Seeking specialized PostgreSQL support or Database Migrations assistance ? Get experts advice for effective data management, query tuning, database migrations and database optimization. Click here for personalized expertise to elevate your database performance and meet your unique business needs.
Subscribe to our Newsletters and Stay tuned for more interesting topics.
