I have written quite a bit on my blog (http://ledgersmbdev.blogspot.com) on how to avoid sproc hell when porting a lot of data access logic to the database. LedgerSMB more or less is moving to a model where all non-service-locator queries (which look up stored procedures) are stored procedures and we believe we have solved most of these problems.

There are significant problems with stored procedures if they aren't used right. VARIADIC stored procedures help, but they don't entirely solve the problem. Fortunately PostgreSQL offers some rich tools for solving some of these problems. For example we make extensive use of named arguments.

But there is a second use here as well, and this is to extend what you can do inside a SQL query and when you combine this with JSON types, you can do quite a lot.

For example, suppose you do something like:

SELECT extract(myjsonfld, 'property1') FROM mytable WHERE id = 123;

There is no extract function in PostgreSQL 9.1-2 (there will be in 9.3 but not sure what the semantics will be), so you could write such a function in Javascript, such that it takes a JSON and string input and outputs a string or JSON object depending on what you want.

This allows you to extend the capabilities of SQL to do whatever you need.