SQL language and PL/pgSQL – a comparison

As shown in Chapter 04, PostgreSQL Advanced Building Blocks, one can write functions in C, SQL, and PL/pgSQL. There are some pros and cons to each approach. One can think of an SQL function as a wrapper around a parameterized SELECT statement. SQL functions can be inlined into the calling subquery leading to better performance. Also, since the SQL function execution plan is not cached 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 the caching of sensitive timestamp 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. If function...