Functions in databases
The main goal is simplifying development tasks
interface (parameters) and implementation (function body)
abstracting from other tasks when implementing a particular function
Traditional languages PostgreSQL
side global variables whole database
effects (volatility categories)
modules own interface namespaces,
and implementation client and server
challenges overhead hiding the query
related to calls from the planner
(inlining) (inlining, subqueries, views)
The main goal of introducing functions in programming is simplifying
development tasks by decomposing them into smaller subtasks. Such
simplification is possible because you can abstract from the big picture
when thinking of a function. For this purpose, the function provides a precise
interface to the outside world (parameters and the return value).
Its implementation (the function body) can change; the caller does not see
these changes and does not depend on them. This ideal situation can be
messed up by the global state (global variables), and you have to keep in
mind that in the DB context the whole database constitutes such a state.
In traditional programming languages, functions are often grouped into
modules (packages, classes for OOP, etc.), which have their own interface
and implementation. This separation into modules can be more or less
arbitrary. In PostgreSQL, there is a fixed boundary between the client and
the server: the server code deals with the database, while the client code
manages transactions. There are no modules (or packages), only
namespaces.
The only disadvantage of extensive use of functions in traditional languages
is function call overhead. It is sometimes overcome by inlining function code
into the calling program. In databases, the consequences can be more
serious: if some part of the query is moved into a function, the planner stops
seeing the big picture and cannot build a good query plan. In some cases,
PostgreSQL can also perform inlining; alternatively, subqueries or views can
be used.