An Overview of the Stored Procedures in PostgreSQL

Table of contents

Reading Time: 4 minutes

As you may know in all the versions up to PostgreSQL 10, it was not possible to create a procedure in PostgreSQL. In PostgreSQL11, PROCEDURE was added as a new schema object which is a similar object to FUNCTION, but without a return value.

Over the years many people were anxious to have the functionality and it was finally added in PostgreSQL. Traditionally, PostgreSQL has provided all the means to write functions (which were called as stored procedures) however, in a function you cannot run transactions. All you can really use is exceptions, which are basically savepoints. The new CREATE PROCEDURE will change all that and provide a functionality to run transactions inside procedural code.

Benefits of Using Stored Procedures

Transaction control allowing us to COMMIT and ROLLBACK inside procedures.
Very helpful for Oracle to PostgreSQL migration, the new procedure functionality can be a significant time saver.
Reduce the number of round trips between applications and database servers. All SQL statements are wrapped inside a function stored in the PostgreSQL database server so the application only has to issue a function call to get the result back instead of sending multiple SQL statements and wait for the result between each call.
Increase application performance because the user-defined functions and stored procedure are pre-compiled and stored in the PostgreSQL database server.

How to Use Stored Procedure in PostgreSQL

Use CREATE PROCEDURE to create a new procedure in PostgreSQL 11, it will allow you to write procedure just like other databases. PROCEDURE is almost the same as FUNCTION without a return value. PROCEDURE is created with the CREATE PROCEDURE statement in PostgreSQL 11.

Listing 1. Creating a stored procedure that returns no value.

CREATE OR REPLACE FUNCTION InsertEmployee(code INTEGER, name VARCHAR(100), email VARCHAR(150), phone VARCHAR(15), city VARCHAR(50), state VARCHAR(2))
 RETURNS void AS $ 
BEGIN INSERT INTO "MyCompany".tb_employee VALUES (code, name, email, telephone, city, state);
 END;
 $ LANGUAGE 'plpgsql';

To create our procedure we use the term “void”, which sets the return without submitting messages. With our SP created we will perform an insertion test to see it up and running, we can do it using the following statement:

SELECT InsertEmployee(5, 'Jhon Mac', 'jhon.mac@gmail.com', '+1 33 323223', 'Rainbow Avenue 12', 'NY');

After the new record insertion we can do a SELECT to verify that the information was added correctly using the following statement:

SELECT code, name, email, phone, city, state
    FROM "MyCompany".tb_employee;

When we declare a PL/pgSQL composed of output parameters, these will be passed optionally with first and last names, such as $Return, exactly the same way as the normal input parameters. An output parameter is a variable that starts out NULL and should be allocated for the execution of the function. The final value of the parameter is what is returned, as we can see an example of mathematical operations in Listing 2.

Listing 2. Returning results with output parameters.

CREATE FUNCTION mathCalcs(x int, y int, OUT sum int, OUT subtract int, OUT multiplication int, OUT division int) AS $
BEGIN
    sum := x + y;
    subtract := x - y;
    multiplication := x * y;
    division := x / y;
END;
              
$ LANGUAGE plpgsql;

In this case we use the OUT command, that gives the output of the results obtained from the input values x and y. Another way to obtain results in the SP’s is returning a function as a TABLE, like in Listing 3.

Listing 3. Returning functions as tables.

CREATE FUNCTION extended_sales(p_itemno int)
RETURNS TABLE(quantity int, total numeric) AS $
BEGIN
    RETURN QUERY SELECT s.quantity, s.quantity * s.price FROM sales AS s
                 WHERE s.itemno = p_itemno;
END;            
$ LANGUAGE plpgsql;

The fields in a table can be generated with new information in a table apart, without the need to create new fields or use other functions to have the same result.

Transaction Control in PROCEDURE

Transaction control allowing us to COMMIT and ROLLBACK inside procedures. CREATE FUNCTION does not support transaction inside the function. This is the main difference between FUNCTION and PROCEDURE in PostgreSQL.

Let’s create a simple stored procedure that handles transactions.

CREATE OR REPLACE PROCEDURE transaction_test()
BEGIN
CREATE TABLE committed_table (id int);
INSERT INTO committed_table VALUES (1);
COMMIT;
CREATE TABLE rollback_table (id int);
INSERT INTO rollback_table VALUES (1);
ROLLBACK;
END;
$ LANGUAGE plpgsql;

Execute the PROCEDURE using CALL statement.

postgres=# CALL transaction_test();                                                      CALL

Check the execution result.

postgres=# \d                                                                         
              List of relations                                                       
 Schema |      Name       | Type  |  Owner                                            
--------+-----------------+-------+----------                                         
 public | committed_table | table | postgres                                          
(1 row)                                                                               
 
postgres=# SELECT * FROM committed_table;
id
----
  1
(1 row)

How to call stored Procedure in Java

To call stored procedures or stored functions in Postgressql from JDBC, you use CallableStatement object, which inherits from Prepared statement Object. The general syntax of calling a stored procedure is as follows:

{?= call procedure_name(param1,param2,...)}

You wrap the stored procedure call within braces ({}). If the stored procedure returns a value, you need to add the question mark and equal (?=) before the call keyword. If a stored procedure does not return any values, you just omit the ?= sign. In case the stored procedure accepts any parameters, you list them within the opening and closing parentheses after the stored procedure’s name.

Then, prepare a stored procedure call and create a CallableStatement object by calling prepareCall() method of the Connection object.

String query = "{CALL get_candidate_skill(?)}";
CallableStatement stmt = conn.prepareCall(query)

Next, pass all the parameters to the stored procedure. In this case, the get_candidate_skill stored procedure accepts only one IN parameter.

stmt.setInt(1, candidateId);

After that, execute the stored procedure by calling the executeQuery() method of the CallableStatement object. It returns a result set in this case.

ResultSet rs = stmt.executeQuery();

Finally, traverse the ResultSet to display the results.

while (rs.next()) {  
System.out.println(String.format("%s - %s",
rs.getString("first_name") + " " +
rs.getString("last_name"),
rs.getString("skill")));}

Conclusion

With this we finish our article, in which we dealt with some important points concerning the creation and use of stored procedures, and we know what we can do with them or not without having a bad writing and bad understanding code.