In postgres, CTEs are optimization barriers, that is, the implementation plans and executes the CTE separately and the main query consumes the result of the CTE. Pure SQL functions can sometimes be inlined, that is, made part of the containing query, but functions in the other languages cannot. In particular, if a function runs queries itself those happen in a new execution context (portal) and don't share anything with the function callers plan.
VOLATILE/STABLE/IMMUTABLE is definitely a choice worth considering and making in an educated way when writing a function.
Also, using WITH to create CTEs makes SQL easy to read, but sometimes will slow down a query considerably. I have a feeling that new releases will make attempts to mitigate this issue in some cases.
In my opinion, try and use as much SQL as you can first. Functions are often an optimization barrier for the postgresql query planner. The postgresql implementation of SQL has a bunch of neat functionality in it that replaces things that often happen in apps, like window functions, and the docs are so great.
They are hard to debug. Try to keep their functionality small.
My experience, overall, is positive. Consider using it if the amount of data transferred to the app and evaluated there is getting absurd. But try to creatively solve the problem with pure SQL first before moving to functions.