Handle overloaded Oracle functions in Aurora PostgreSQL-Compatible - AWS Prescriptive Guidance
SummaryPrerequisites and limitationsToolsEpicsRelated resources

Handle overloaded Oracle functions in Aurora PostgreSQL-Compatible

Created by Sumana Yanamandra (AWS)

Environment: PoC or pilot

Source: Oracle Database

Target: Aurora PostgreSQL-Compatible

R Type: Replatform

Workload: Oracle

Technologies: Databases; Migration

AWS services: Amazon Aurora

Summary

The code you migrate from an on-premises Oracle database to Amazon Aurora PostgreSQL-Compatible Edition might include overloaded functions. These functions have the same definition—that is, the same function name and the same number and data type of input (IN) parameters—but the data type or the number of output (OUT) parameters might differ. 

These parameter mismatches can cause problems in PostgreSQL, because it’s difficult to determine which function to run. This pattern illustrates how to handle overloaded functions when you migrate your database code to Aurora PostgreSQL-Compatible.

Prerequisites and limitations

Prerequisites 

  • An Oracle database instance as your source database

  • An Aurora PostgreSQL-Compatible DB instance as your target database (see instructions in the Aurora documentation)

Product versions

Tools

AWS services

Other tools

  • Oracle SQL Developer is a free, integrated development environment for working with SQL in Oracle databases in both traditional and cloud deployments. 

  • pgAdmin is an open-source management tool for PostgreSQL. It provides a graphical interface that helps you create, maintain, and use database objects.

Epics

TaskDescriptionSkills required
Create a function in PostgreSQL that has one input parameter and one output parameter.

The following example illustrates a function named test_overloading in Aurora PostgreSQL-Compatible. This function has two parameters: one input text parameter and one output text parameter.

CREATE OR REPLACE FUNCTION public.test_overloading(
         str1 text,
         OUT str2 text)
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE
AS $BODY$
DECLARE
BEGIN
         str2 := 'Success';
    RETURN ;
    EXCEPTION
        WHEN others THEN
             RETURN ;
END;
$BODY$;
Data engineer, Aurora PostgreSQL-Compatible
Run the function in PostgreSQL.

Run the function that you created in the previous step.

select public.test_overloading('Test');

It should display the following output.

Success
Data engineer, Aurora PostgreSQL-Compatible
TaskDescriptionSkills required
Use the same function name to create an overloaded function in PostgreSQL.

Create an overloaded function in Aurora PostgreSQL-Compatible that uses the same function name as your previous function. The following example is also named test_overloading, but it has three parameters: one input text parameter, one output text parameter, and one output integer parameter.

CREATE OR REPLACE FUNCTION public.test_overloading(
         str1 text,
         OUT str2 text,
         OUT num1 integer)
    LANGUAGE 'plpgsql'
 
    COST 100
    VOLATILE
AS $BODY$
DECLARE
str3 text;
 
BEGIN
 
         str2 := 'Success';
         num1 := 100;
 
    RETURN ;
    EXCEPTION
        WHEN others THEN
             RETURN ;
END;
$BODY$;
Data engineer, Aurora PostgreSQL-Compatible
Run the function in PostgreSQL.

When you run this function, it fails with the following error message. 

ERROR: cannot change return type of existing function
HINT:      Use DROP FUNCTION test_overloading(text) first.

This happens because Aurora PostgreSQL-Compatible doesn’t support function overloading directly. It can’t identify which function to run, because the number of output parameters is different in the second version of the function, although the input parameters are the same.

Data engineer, Aurora PostgreSQL-Compatible
TaskDescriptionSkills required
Add INOUT to the first output parameter.

As a workaround, modify the function code by representing the first output parameter as INOUT.

CREATE OR REPLACE FUNCTION public.test_overloading(
         str1 text,
         INOUT str2 text,
         OUT num1 integer)
    LANGUAGE 'plpgsql'
 
    COST 100
    VOLATILE
AS $BODY$
DECLARE
str3 text;
BEGIN
 
         str2 := 'Success';
         num1 := 100;
 
    RETURN ;
    EXCEPTION
        WHEN others THEN
             RETURN ;
END;
$BODY$;
Data engineer, Aurora PostgreSQL-Compatible
Run the revised function.

Run the function that you updated by using the following query. You pass a null value as the second argument of this function, because you declared this parameter as INOUT to avoid the error. 

select public.test_overloading('Test', null);

The function is now created successfully.

Success, 100
Data engineer, Aurora PostgreSQL-Compatible
Validate the results.

Verify that the code with the overloaded function was converted successfully.

Data engineer, Aurora PostgreSQL-Compatible

Related resources