Summary

PostgreSQL provides a complete programming language called PL/pgSQL, which is integrated with the PostgreSQL trigger system. The PL/pgSQL and SQL languages can be used to code very complex logic. One should use the SQL functions when possible. With the introduction of advanced query writing techniques such as window function and lateral Join in PostgreSQL, one can write very complex logic using only the standard SQL language.

There are several parameters in PostgreSQL for controlling the function behavior; these parameters are applicable to the PL/pgSQL and SQL functions as well. For example, SECURITY DEFINER and SECURITY INVOKER define execution security context and privileges. A function planner parameters help the planner to generate execution plans. These parameters are COST, LEAKPROOF, and ROWS.

The dynamic SQL technique enables the developers to build SQL statements dynamically at runtime. One can create general purpose, flexible functions because the full text of an SQL statement...