So, we make heavy use of them for things like reporting and providing a clean interface to multiple different client programs and APIs.
That said, I kind of view them as an anti-pattern unless you treat your development of functions and stored procedures as rigorously as you handle all the rest of your development. That means using proper migration tools, using versioning, and especially rigorous testing.
I used to work on a system that require a stored procedure for every database call - it became very annoying. I understand that stored procedures used to be the only way to ensure a query was optimised, but that isn't the case these days.
Don't most database libraries convert parameterized queries into stored procedures at runtime? None of the overhead of manually managing stored procedures, but all of the safety benefits.
That said, I kind of view them as an anti-pattern unless you treat your development of functions and stored procedures as rigorously as you handle all the rest of your development. That means using proper migration tools, using versioning, and especially rigorous testing.
Towards the testing end, we've found pgTAP to be extremely helpful: http://pgtap.org/documentation.html .
I'd love to hear other folks feedback about their practices here.