PL\pgSQL Conversion Gotchas : Functions with Out parameter and return type.

If you’re fascinated by the internals of PL/pgSQL processing and want to handle it efficiently, especially if you’re migrating procedural logic from another database, be sure to check out the previous posts in the series on PL/pgSQL Conversion Gotchas – Part 1 and Part 2

In Oracle, we used to build multiple functionalities within procedural blocks, either in functions or procedures. In these blogs, we will explore some of the gotchas that we need to take care of.

Migrating Functions with Out Parameters and Return Types

Oracle allows functions to have arguments defined as out parameters and also return additional status arguments or execution statuses as part of function execution. Though it is not best practice, when migrating legacy code bases, we encounter all types of scenarios.

Below is one such sample function with out params and return type.

create or replace function func_test1
(var1 in integer,var2 out integer )
return integer
as
begin
var2 := var1 + 1;
return var2*2;
end;

If we run it in Oracle, it will produce two values: one as an out parameter and another as a return value

set serveroutput on;
declare
  l_out_var2 integer; 
  l_out_ret integer;  
begin
  l_out_ret := func_test1(1,l_out_var2 );
  dbms_output.put_line('Out Value - ' || l_out_var2);
  dbms_output.put_line('Return Value - ' || l_out_ret);
end;
/
/

Let’s attempt to convert the sample function to PostgreSQL using the PL/pgSQL language. As a reference, we’ll aim to build similar code logic as in the Oracle sample, creating a function in PostgreSQL with PL/pgSQL that includes both an out parameter and a return type.

create or replace function func_test1
(var1 in integer,var2 out integer )
returns integer
language plpgsql
as
$$
begin
var2 := var1 + 1;
return var2*2;
end;
$$;

Let’s proceed with deploying it within the PostgreSQL database

In PostgreSQL, when there’s a requirement to return multiple values, we typically transform them into record types with attributes corresponding to the types we need to return. In our case, since the Oracle function produces multiple values, we’ll include additional out parameters and intentionally skip an explicit return statement.

create or replace function func_test1
(var1 in integer,var2 out integer ,  
l_out_ret out integer)
language plpgsql
as
$$
begin
var2 := var1 + 1;
l_out_ret:= var2*2;
end;
$$;

Conclusion :-

Oracle allows creating functions that can have out parameters along with return types, but when migrating similar functions to PostgreSQL, we might not achieve exactly the same characteristics. As a workaround, we will have to move the return type parameters as additional out parameters in the migrated functions.

If you really enjoyed the blog and are exploring courses to master PL/pgSQL development on PostgreSQL, please check out my Live courses.