The ability to write functions in PostgreSQL is an amazing feature. One can perform any task within the scope of the database server. These tasks might be related directly to data manipulation such as data aggregation and auditing, or used to perform miscellaneous services such as statistics collection, monitoring, system information acquisition, and job scheduling. In this chapter, our focus is on the PL/pgSQL language. PL/pgSQL is the default procedural language for PostgreSQL and it is full featured. As mentioned earlier, in Chapter 04, PostgreSQL Advanced Building Blocks, PL/pgSQL is installed by default in PostgreSQL.
PL/pgSQL has been influenced by the PL/SQL language, which is the Oracle stored procedural language. PL/pgSQL is a complete procedural language with rich control structures and full integration with the PostgreSQL trigger, index, rule, user-defined data type, and operator objects. There are several advantages to using PL/pgSQL; they are as follows:
- It is easy to learn and use
- It has very good support and documentation
- It has very flexible result data types, and it supports polymorphism
- It can return scalar values and sets using different return methods
The topics that will be covered in this chapter include:
- SQL language and PL/pgSQL – a comparison
- PostgreSQL function parameters
- The PostgreSQL PL/pgSQL control statements
- Function predefined variables
- Exception handling
- Dynamic SQL