The SQL language and PL/pgSQL – a comparison

As illustrated in Chapter 4, PostgreSQL Advanced Building Blocks, we can write functions in C, SQL, and PL/pgSQL. There are some pros and cons to each approach. You 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 is often better in performance 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 at http://www.postgresql.org/docs/current/interactive/plpgsql-implementation.html. Finally, with the introduction of CTE, recursive CTE, window functions, and LATERAL JOINS, you can perform complex logic using only SQL. If...