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 functions 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 function behavior; these parameters are applicable to the PL/pgSQL and SQL functions as well. For example, SECURITY DEFINER
and SECURITY INVOKER
define the execution security context and privileges. Function's planner parameters help the planner to generate execution plans. These parameters are COST
, LEAKPROOF
, and ROWS
. Configuration related parameters can also be applied to functions.
PL/pgSQL is a fully fledged language, there are statements for assignment, conditionals, iterations, and for exception...