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.
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.
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.
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.
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.
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.
Execution
Use CALL to execute the stored procedure.
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.
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.
You can also call (nest) another stored procedure in a stored procedure.
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.
The following is the transition of the my_tbl table when the above is executed.
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.
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.
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.
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.
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.
In this article, we explained stored procedure of PostgreSQL. Stored procedures are advantageous for improving application development efficiency as well as for faster processing.
Fujitsu Enterprise Postgres is designed for developers, data architects, and DBAs seeking the efficiency of a leading open source based database technology with the enhanced security and performance features plus support required in a serious business environment.
Read our most recent articles regarding all aspects of PostgreSQL and FUJITSU Enterprise Postgres.