Stored procedures improve portability when migrating from databases that support them, such as Oracle. Migration to PostgreSQL is definitely worth considering if you would like to reduce costs, especially related to licensing. In this article, we will explain stored procedures and actual usage examples, and provide a quick summary of stored procedure migration from Oracle to PostgreSQL.
What is a stored procedure?
A stored procedure is a series of SQL statements you can store in the database server in advance. Adopting this feature allows you to execute a series of processes simply by calling the stored procedure from various clients and applications. The benefit becomes obvious when you want to perform general-purpose processing (such as aggregating daily sales) on the database from different applications. You only need to execute the stored procedure on the database server.
Stored procedures can be written in different languages depending on the database system. In PostgreSQL, the following languages are supported.
- Procedural languages (PL/pgSQL, PL/Python, PL/Tcl, PL/Perl)
- SQL
- C
Faster processing
Stored procedures can execute multiple SQL statements in a single request.
Normally, when a client runs an application, SQL statements are sent from the client to the server one by one, where results are received each time before the next SQL is sent. This is time-consuming because this exchange is repeated until the task is complete. If stored procedures are used, a single call of a procedure from a client will execute multiple SQL processes, and only the final execution result is sent to the client. This reduces network load and time.
Without stored procedures
SQL statements being sent to server one-by-one
With stored procedures
SQL statements on a single procedure call
It is however important to note that there may be times when the database server is overloaded. Example of such cases include when stored procedures are called from multiple clients all at once or when a relatively large-scale stored procedure is used. Processing that run effectively with stored procedures need to be analyzed and selected with careful consideration to performance.
Standardization of general-purpose processing
Multiple applications may perform similar processing on the database. By separating such similar processing from applications and consolidating it as a stored procedure in the database, the processing can be standardized as a common procedure. Application development efficiency is improved, as there is no need to include the processing in applications. Maintainability is also greatly improved. Whenever a change needs to be made to the process, modification is needed only on the standardized stored procedure.
Commonly executed processes such as aggregating current inventory or aggregating daily sales are fit for stored procedures.
Without stored procedures
Processing scattered throughout apps
With stored procedures
Processing centralized in stored procedure
Characteristics of stored procedures
Parameters including IN, INOUT, and VARIADIC can be specified for a stored procedure. OUT cannot be used. Another characteristic is that a stored procedure does not return a value, thus, it does not have a RETURNS clause. Instead, a value can be returned with INOUT. To call a stored procedure, CALL is used. COMMIT and ROLLBACK can be used for transaction control.
Examples of stored procedure definition and execution
This section describes examples of stored procedure definition and execution. In the definition examples below, PL/pgSQL is used as the procedural language.
Definition
Stored procedures are defined with CREATE PROCEDURE. In the example below, the value is returned by specifying INOUT.
CREATE PROCEDURE proc1(INOUT p1 TEXT)
AS $$
BEGIN
p1 := '!! ' || p1 ||' !!';
RAISE NOTICE 'Procedure Parameter: %', p1 ;
END;
$$
LANGUAGE plpgsql ;
Execution
Use CALL to execute the stored procedure.
mydb=# CALL proc1 ('Stored Procedure supported in PostgreSQL');
NOTICE: Procedure Parameter: !! Stored Procedure supported in PostgreSQL !!
p1
--------------------------------------------------------
!! Stored Procedure supported in PostgreSQL !!
(1 row)
Example of stored procedure with transaction control
Stored procedures can implement COMMIT/ROLLBACK transaction control.
Create a stored procedure that describes COMMIT/ROLLBACK like below and check the operation of COMMIT/ROLLBACK.
The example below iterates a value from 1 to 100 insert it into my_tlb - when the value is multiple of 10, the procedure commits the insert, otherwise it rolls back.
CREATE PROCEDURE proc2()
LANGUAGE plpgsql
AS $$
BEGIN
FOR idx IN 1..100 LOOP
INSERT INTO my_tbl(col_num) VALUES(idx);
IF idx % 10 = 0 THEN A
COMMIT; B
ELSE
ROLLBACK; C
END IF;
END LOOP;
END
$$;
A Check if idx is multiple of 10
B idx is multiple of 10 - commit the insert
C idx is not multiple of 10 - roll back
Let's execute the stored procedure and see the result.
In the example below, only the multiples of 10 are inserted into the my_tbl table, and we can see that COMMIT/ROLLBACK worked.
mydb=# CREATE TABLE my_tbl(col_num integer);
CREATE TABLE
mydb=# CALL proc2();
CALL
mydb=# SELECT * FROM my_tbl;
col_num
--------
10
20
30
40
50
60
70
80
90
100
(10 rows)
You can also call (nest) another stored procedure in a stored procedure.
CREATE PROCEDURE proc3() LANGUAGE plpgsql AS $$
BEGIN
INSERT INTO my_tbl VALUES(1); A
CALL proc4();
INSERT INTO my_tbl VALUES(4); D
COMMIT;
END;
$$;
CREATE PROCEDURE proc4() LANGUAGE plpgsql AS $$
BEGIN
INSERT INTO my_tbl VALUES(2); B
ROLLBACK;
INSERT INTO my_tbl VALUES(3); C
END;
$$;
Now let's execute the stored procedure and check the execution result.
In the above example, proc4 is called from proc3, but proc4 does not become a subtransaction, and we can observe that the caller's process A has been rolled back by proc4 and has become invalid.
mydb=# DELETE FROM my_tbl;
DELETE 10
mydb=# CALL proc3();
CALL
mydb=# SELECT * FROM my_tbl; *
col_num
-------
3
4
(2 rows)
* Due to ROLLBACK of proc4, neither A nor B were inserted
The following is the transition of the my_tbl table when the above is executed.
Transaction progress
Migrating stored procedures from Oracle to PostgreSQL
Up to PostgreSQL 10, when migrating Oracle stored procedures to PostgreSQL, it was necessary to convert to functions. Since PostgreSQL 11, stored procedures are supported, so there are reduced barriers to migrate.
Here is an example of migrating an Oracle stored procedure to PostgreSQL. In the example below, you only need to modify the red part for migration.
Oracle
PostgreSQL
Remarks
In the example above, the number of rows in my_tbl2 table is checked in advance - if the table has 10 or more rows, then an error is raised, otherwise the specified id is insert in a new row.
Note
Fujitsu Enterprise Postgres has a service to support migration from other companies' databases. Our migration support service provides comprehensive support from migration assessment of applications such as stored procedures to asset migration and installation.
Restrictions on transaction control
PostgreSQL and Oracle have different transaction control specifications. PostgreSQL has the restriction below regarding transaction control, so it needs to be taken into account when migrating from Oracle.
When a stored procedure is called inside a function or an explicit transaction and COMMIT/ROLLBACK is executed within that stored procedure, it will result in an error.
mydb=# BEGIN; A
BEGIN
mydb=# CALL p_samp3();
ERROR: invalid transaction termination. B
A Start a transaction
B If p_samp3 tries to commit or roll back, an error occurs
COMMIT/ROLLBACK cannot be written between BEGIN and EXCEPTION. Instead, use EXCEPTION to make necessary processing into a subblock from BEGIN to END, and write COMMIT/ROLLBACK outside the subblock.
Incorrect syntax
Correct syntax
In this article, we explained stored procedure of PostgreSQL. Stored procedures are advantageous for improving application development efficiency as well as for faster processing.
