<img height="1" width="1" style="display:none;" alt="" src="https://px.ads.linkedin.com/collect/?pid=2826169&amp;fmt=gif">
Start  trial

    Start trial

      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.

      Related PostgreSQL Insider articles

      This article explains how PostgreSQL Foreign Data Wrappers can access various external data sources such as Oracle, MySQL, SQL Server, and MongoDB, among others.
      This is the first part on our discussion of how to access Oracle databases using PostgreSQL's foreign data wrapper oracle_fdw.
      With replication, you can build a highly available system that can continue operating even if a failure occurs. In this article, we discuss how streaming replication works, and how you can configure it in PostgreSQL.
      oracle_fdw (part 1)

      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.

      Brochure

      See how Fujitsu Enterprise Postgres enhances PostgreSQL providing 5 key enterprise capabilities for your mission-critical applications.

      1st  page brochure FUJITSU Enterprise Postgeres - Fujitsu's enhanced open source PostgreSQL
      View brochure

      Read our Latest Blogs

      Read our most recent articles regarding all aspects of PostgreSQL and FUJITSU Enterprise Postgres.