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 can be used to perform miscellaneous services such as statistics collection, monitoring, system information acquisition, and job scheduling.
In this chapter, our focus is the PL/pgSQL language. PL/pgSQL can be considered as the default PostgreSQL, which is a full-fledged procedural language. As mentioned earlier in Chapter 4, 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 of using PL/pgSQL; they are as follows:
- It is easy to use and learn
- 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
As shown in Chapter 4, PostgreSQL Advanced Building Blocks, one can write functions in C, SQL, and PL/pgSQL. There are some pros and cons of each approach. One can think of an SQL function as a wrapper around a parameterized SELECT statement. SQL functions can be in-lined into the calling subquery leading to a better performance. Also, since the SQL function execution plan is not cashed as in PL/pgSQL, it often behaves better than PL/pgSQL. Moreover, caching in PL/pgSQL can have some surprisingly bad side effects such as caching of sensitive time stamp values, as shown in the documentation that can be found at http://www.postgresql.org/docs/current/interactive/plpgsql-implementation.html.
Finally, with the introduction of CTE, recursive CTE, window functions, and LATERAL JOINS, one can perform complex logic using only SQL.
The PL/pgSQL function execution plan is cached; caching the plan can help in reducing the execution time, but it can also hurt it in case the plan is not optimal for the provided function parameters.
From a functionality point of view, PL/pgSQL is much more powerful than SQL for writing functions. PL/pgSQL supports several features that the SQL functions cannot support, including the following:
- It provides the ability to raise exceptions as well as to raise messages at different levels such as notice and debug.
- It supports constructing of dynamic SQL using the
EXECUTEcommand. - It provides
EXCEPTIONhandling. - It has a complete set of assignment, control, and loop statements.
- It supports cursors.
- It is fully integrated with the PostgreSQL trigger system. SQL functions cannot be used with triggers.