Summary: in this tutorial, you will learn about PostgreSQL stored procedures for developing functions in PostgreSQL.
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 or stored procedure.
PostgreSQL divides the procedural languages into two main groups:
- Safe languages can be used by any users. SQL and PL/pgSQL are the safe languages.
- Sand-boxed languages are only used by superusers because sand-boxed languages provide the capability to bypass security and allow access to external sources. C is an example of a sandboxed language.
Advantages of using PostgreSQL stored procedures
The stored procedures bring many advantages as follows:
- 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.
- Reusable in many applications. Once you develop a function, you can reuse it in any applications.
Disadvantages of using PostgreSQL stored procedures
Besides the advantages of using stored procedures, there are some caveats:
- Slowness in software development because stored procedure programming requires specialized skills that many developers do not possess.
- 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 this tutorial, we have introduced you to PostgreSQL stored procedures and discussed the pros and cons of using stored procedures in PostgreSQL.