Summary: in this tutorial, you will learn about PostgreSQL stored procedures for developing functions in PostgreSQL.
PostgreSQL allows you to extend the database functionality with user-defined functions by using various procedural languages, which often referred to as stored procedures.
The store procedures define functions for creating triggers or custom aggregate functions. In addition, stored procedures also add many procedural features e.g., control structures and complex calculation. These allow you to develop custom functions much easier and more effective.
It is possible to call a procedural code block using the DO command without defining a function.
PostgreSQL divides the procedural languages into two main groups:
- Safe languages can be used by any users. SQL and PL/pgSQL are safe languages.
- Sand-boxed languages are only used by superusers because sand-boxed languages provide the capability to bypass security and allow access external sources. C is an example of a sand-boxed language or unsafe language.
By default, PostgreSQL supports 3 procedural languages: SQL, PL/pgSQL, and C. You can also load other procedural languages e.g.,Perl, Python, and TCL into PostgreSQL using extensions.
Advantages of using PostgreSQL stored procedures
The stored procedures bring many advantages as following:
- Reduce the number of round trips between application 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 user-defined functions pre-compiled and stored in the PostgreSQL database server.
- Be able to reuse in many applications. Once you develop a function, you can reuse it in any applications.
Disadvantages of using PostgreSQL stored procedures
Besides those advantages of using stored procedures, there are some caveats as follows:
- Slow in software development because it requires specialized skills that many developers do not possess.
- Make it difficult to manage versions and hard to debug.
- May not be portable to other database management systems e.g., MySQL or Microsoft SQL Server.
In the next tutorial, we will show you how to develop user-defined functions using PL/pgSQL, which comes with PostgreSQL by default.
In addition, PL/pgSQL is similar to PL/SQL (more on PL/SQL Tutorial), which is easy to learn.
In this tutorial, we have introduced you to PostgreSQL stored procedures and discussed about the pros and cons of using stored procedures in PostgreSQL.
Let’s move to the next tutorial to develop the first user-defined function using PL/pgSQL procedural language.