I originally tried this with a project I am working on, moving as much of the logic as possible into the DB. But abandoned the effort quickly in part because the logic I need to implement was simply too complex and was better expressed & validated in a client side language.

plpgsql performance is a serious issue for anything non trivial.

debugging is slow and challenging.

General rules I apply with procs

* try to keep procs to SQL, not plpgsql. More performant usually and sometimes pg will inline these. But sometimes I've found forcing it as plpgsql is good too, as it effectively 'fences' the query planner just like CTEs used to. As supportive as I am with pg's philosophy on querying hinting, sometimes you need to press your thumb on the scales a bit.

* procs for very complex and subtle queries where performance is not such a concern but readabiltiy of client code or dependent queries is useful. Like a client side function, something whose inputs and outputs are easy to reason about and you can easily build client side regression tests to validate.

* consider using plpgsql proc instead of recursive CTE for things where CTE recursion is needed. I've found for some types of jobs, plpgsql outperforms a recursive CTE. I think bc query planning for recursive CTES is very difficult.