6Functions and Stored Procedures

Functions and procedures enable developers to package frequently used SQL statements and manage their execution using IF/THEN/LOOP and other control statements. Functions and procedures modularize and encapsulate business logic, making it easy to bring the logic to the data, rather than moving all the data from the database to the applications first. This minimizes the roundtrips between the application and data, significantly improving transaction performance and latency.Last, but not least, functions and stored procedures are important security mechanisms that can provide secure, selective access to data, without exposing the tables themselves.Functions have been supported since Postgres95, before it became PostgreSQL. Procedures were added in PostgreSQL Version 11.In this chapter, we will discuss the differences between functions and procedures, both in general and specifically in PostgreSQL. We will also examine functions and procedures that utilize...