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 logic can be implemented in SQL, use an SQL function instead of PL/PGSQL.
The PL/pgSQL function execution plan is...
