When I didn't know any better I wrote a finite state machine in it.

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.