[memset]

I have always wondered why people have an aversion to stored procedures. They are a programming language in themselves, so you could pass all of your parameters (owner, some_condition, other_condition) into a proc and, depending the logic, return a different cursor to a different query.

[zzzeek]

it's because the stored procedure development model lacks the tools in order to make integrating with an application-level domain model simple. You end up needing to write not just one persistence layer, that of marshaling your object model to and from SQL statements, but two - all the SQL statements behind your stored procedure layer, and a second to move all the data between the SPs and your object model. To make matters worse, the stored procedures must be written completely by hand without the benefit of any in-application schemas to help compose statements.

One reason for the variety of opinion on this is that different developers make more or less use of domain models in the first place. Those who are accustomed to writing all SQL completely by hand with no helpers at all, and not working with a domain model tend to view the stored procedure approach as equivalent. Those who are accustomed to having at least some simple marshaling layers like a construct that generates an INSERT statement given a list of column names see the SP approach as more tedious since simple techniques like that are usually not easily available, at least in more old school SP languages like TRANSACT-SQL and PL/SQL.

All of that said, I do think this is a problem that can possibly be solved. Postgresql allows SPs to be written in many languages, including Python. I have an ongoing curiousity about the potential to integrate a Python-based object relational system into a stored procedure system. But it might end up looking like EJBs.

[hcarvalhoalves]

The problem with stored procedures is managing it. You have to maintain another, separate codebase. You need to make sure those procedures are "installed" and up to date. Plus, DBMSs don't have a concept of "versions", and those procedures are treated as data. In short, it's hell.

That's one of the pain points RethinkDB is trying to solve, since you write your queries in whatever application language you use and it's parsed and executed in the cluster.