There are various scenarios where functions can speed up query performance. Most notably, PL/pgSQL functions handle SQL statements like prepared statements. To be precise it uses the SPI managers
The prepared statement also provides a place for caching an execution
plan if it is found that generating a custom plan for each execution is not helpful.
The manual on
PREPARE has guidelines:
Prepared statements potentially have the largest performance advantage
when a single session is being used to execute a large number of
similar statements. The performance difference will be particularly
significant if the statements are complex to plan or rewrite, e.g. if
the query involves a join of many tables or requires the application
of several rules. If the statement is relatively simple to plan and
rewrite but relatively expensive to execute, the performance advantage
of prepared statements will be less noticeable.
That should also say something to your question about "a common operation that's a little more complicated".
Obviously, you can also achieve these benefits with prepared statements. But prepared statements are limited to a single DML statement. Functions can do a lot more.
But be wary of nesting functions (that cannot be inlined) in queries. Since those are planned separately (posing as optimization barrier). That might stand in the way of finding the most efficient query plan. Like in your example, if you nest the function in a query that looks up many user names at once, that's going to be a lot more expensive than a join to the lookup table.
Only declare functions
IMMUTABLE that are actually immutable. Exceptions apply, like when you need to build an expression index (and know what you are doing). Example:
Else, you can actually harm performance with an incorrect volatility declaration, as it can prohibit function inlining. Quoting the Postgres Wiki on inlining:
if the function is declared
IMMUTABLE, then the expression must not invoke any non-immutable function or operator
That's merely a corrective to the currently accepted answer. But you are not primarily concerned with performance, rather with "good style". And that very much depends on the use case.
If you have a single app working with a your DB, then it's mostly a decision of language skill and taste where to encapsulate logic - in the app or in the DB. But if you have several apps working with the same DB, then it can make a lot of sense to encapsulate shared logic in the DB (with server-side functions or other means of SQL like table constraints, VIEWs, etc.) Even a trivial function like the one you display can make sense then.