Stored Procedure OUT Parameters in PostgreSQL 14

PostgreSQL global development group generally announces a new release every year, with several new features. With an increase in the number of contributions and contributors, the new version PostgreSQL 14 released on September 30th, 2021, with several important features.  As a developer and a database migration expert, I am happy to say that there are several developer friendly features with PostgreSQL 14. In this article, I would like to discuss and demonstrate about Stored Procedure OUT parameters in PostgreSQL 14. Developers migrating databases to PostgreSQL can utilize this feature to reduce the overall time involved in making application level changes instead. Let us now understand stored procedure out parameters in PostgreSQL 14. 

Parameters supported with previous releases of PostgreSQL

In earlier versions of PostgreSQL, PROCEDURE only supported (1) IN, (2)IN OUT parameters. While migrating from Oracle to PostgreSQL, we had to convert all the OUT parameters in Oracle as IN OUT parameters in PostgreSQL. In addition to that. we had to set the argument value while calling the procedure from a Java application. For this reason, in earlier releases of PostgreSQL, we had to not only perform changes to a PostgreSQL PROCEDURE definition but also some application code level changes.

PostgreSQL 14 adds support for OUT parameters in Stored Procedures

Starting from PostgreSQL 14, the OUT parameter support has been added for Stored procedures. This is a good news for one of our customers migrating from Oracle to PostgreSQL, as we don't have to make any huge changes to the procedure definition or the application code.PostgreSQL 14 supports OUT parameters for Stored Procedures

The original requirement of our customer was to reduce the application level changes while porting to PostgreSQL. While this feature appears to satisfy such requirements, one may come across an error while calling the procedure with OUT parameters from a Java application.

org.postgresql.util.PSQLException: ERROR: invalid input syntax for type integer: "null" Where: unnamed portal parameter $1 = '...'

Before discussing the solution to the above error, let me demonstrate a scenario to explain the behaviour in Oracle and upon conversion to PostgreSQL. Consider an example Oracle procedure as following - 

CREATE OR replace PROCEDURE Test_proc(a OUT INT)
IS
  result INT;
BEGIN
    result := 24;
    a := result;
END;

The application code used to call this Oracle procedure appears as following.

CallableStatement cs = connection.prepareCall("call test_proc(?)");
cs.registerOutParameter(1, Types.INTEGER);
cs.execute();
System.out.println(cs.getInt(1));

Following is the output we see upon calling this Oracle procedure with OUT parameter. 

Oracle Output
-------------
24

Now, let us convert the above mentioned Oracle procedure to PostgreSQL. As I am using PostgreSQL 14, i can specify the OUT procedure. Thus, the converted PostgreSQL stored procedure appears as follows. 

CREATE OR replace PROCEDURE test_proc(a OUT INT)
AS
$
DECLARE
    result INT;
BEGIN
    result:= 24;
    a:=result;
END;
$ LANGUAGE plpgsql;

Following is the application code that is used to call the PostgreSQL (14) procedure.

CallableStatement cs = connection.prepareCall("call test_proc(?)");
cs.registerOutParameter(1, Types.INTEGER);
cs.execute();
System.out.println(cs.getInt(1));

We now see an error in the output as seen in the following log. 

PostgreSQL Output
-----------------
org.postgresql.util.PSQLException: ERROR: invalid input syntax for type integer: "null"
Where: unnamed portal parameter $1 = '...'

If you notice carefully, we have used the same application code against PostgreSQL and it resulting in an error. This is because, the application is calling the procedure via PrepareCall by not passing any arguments to it. When I tested the PostgreSQL behavior by using an anonymous code block as seen in the following code block, it works as expected. 

postgres=# DO
$
DECLARE
result int;
BEGIN
CALL test_proc(result);
RAISE NOTICE '%', result;
END
$;
NOTICE:  24
DO

But to achieve the same behavior in the application, we also need to pass some value as an argument value. As the parameter is an OUT parameter, we do not need to pass any variable, so we can simply pass a null value as argument. So, we can call the procedure using CALL test_proc(null), which should also be giving the desired results.

As seen in the following log, I have set the 1st argument value as null in the application code and I could now see the desired Oracle behavior.

CallableStatement cs = connection.prepareCall("call test_proc(?)");
cs.setNull(1,Types.NULL);
cs.registerOutParameter(1, Types.INTEGER);
cs.execute();
System.out.println(cs.getInt(1));

Following is the output as expected.

PostgreSQL Output
-----------------
24

Yay! Finally, we got the desired output as seen with Oracle but with a minor change in the application code but without any special changes to the converted PostgreSQL stored procedure.

Conclusion

While migrating an application from Oracle to PostgreSQL, it may not be always possible to avoid application code level changes. PostgreSQL 14 has definitely addressed some of the major challenges for developers and admins. But an important point to note is that the code that worked for Oracle may not directly work for PostgreSQL. Sometimes, we have to follow the PostgreSQL style which could lead into modifying or adding a few lines of code to the application for portability to PostgreSQL. However, this should never be considered as huge challenge when you have experienced migration experts supporting you during migrations. If you are looking to migrate to PostgreSQL or get an estimated amount of time and complexity involved in migrating your databases, please contact us today or send us an email to sales@migops.com.

If you are looking for any of the following services offered by MigOps, please submit the following form today and one of our experts will be in touch with you. 

2 thoughts on “Stored Procedure OUT Parameters in PostgreSQL 14”

Leave a Comment

Your email address will not be published.

Scroll to Top