PostgreSQL vs MySQL: an apples to oranges comparison

Maybe. but I don't think I passed judgement on that issue. What I think I was saying was that if you have multiple applications writing to the same relation, you have to assume lax data controls on the part of every other writing app. I suspect, as I put in the article, that your view is that the API level should be app-level only, with web services instead of db queries.

Again, would be great if its possible. Probably one very important part of the workflow would be not to allow the DEVs to code in a MVC framework, essentially VC only, or just vestigial M like anything goes and rely solely on the DB for all data modeling. Otherwise each environment will have a different, probably incompatible, model.

Ok, let's look carefully at the role an ORDBMS plays in this, it is as an information model not a behavior model. The former is more or less a proper subset of the latter.

So things we can model are storage and retrieval stuff:

1) Save a GL transaction. Is it balanced? Throw error if not.

2) What is the balance of the checking account?

3) Store the info assuming we dispose of asset '12345-56665' by selling it for $100.

Things we should not do:

1) Presentation layer stuff

2) i18n stuff

3) Anything non-transactional (emails etc).

But the point is that the former category provides a save API for integration with other apps. The latter category is less important for integration. If the tools are there, however you can decide when and where they are appropriate. If they aren't there you don't have that choice.

One huge tradeoff though is that as soon as you go this direction you give up on portability and get really truly locked into one ORDBMS.

when your code goes into RDBMS engine, then it's no longer just a rdbms engine

There's an important tradeoff being discussed here, which is, "can we get directly the data we want from the query", versus, "do we need to load all the data into our app first and filter it there". This is of course the critical thing that a lot more people need to learn, and the work I do with SQLAlchemy is all about this. But in the SQLA approach, we use Python constructs on the app side which expand into SQL functions when rendered in a query. The effect is very similar to that which I see in most of the examples in your posts.

While I think advanced data models and rich SQL-side functionality are essential, the usage of stored procedures is IMHO not the only way to get there. In practice I often use a mix of both, depending on how verbose the function needs to be.

Keeping SQL functions as app-side constructs has the advantage of source code management. It's easier to support multiple kinds of backends (I run against PG and SQL Server a lot) since you aren't tied to a stored procedure language. There's no need to emit new stored procedure definitions to the database in order to support new features of the application. You don't have the issue of updating a stored procedure on the database side such that multiple application versions, targeted to different versions of the database function, still continue to function. I think there are ways to approach these problems in favor of SPs, but they require some thought on how the source code is maintained, managed, and deployed. For now I've just stuck with keeping most SQL functions on the app side.

The big namespacing problems I see are, what if two different kinds of "classes" want to have the same method name ? The definition of a PG function here creates a name that's global to the whole schema - this suggests we may want names that are qualified with a "class name". And what if you do in fact need two versions of the same function present to support different application versions ? In that case maybe we want to qualify the names of the functions with version ids as well. This actually sets up a great opportunity to use an application side system of rendering class/version qualified SQL names in response to plain names on the app side.

I guess my point is that the "app logic in stored procedures" approach is interesting, it has some management/deployment issues that also might be interesting to solve, but app-rendered SQL when using an effective enough app-side toolkit can solve the problem just as well in most cases.

I still fail to see why anybody would want that or even the default 'if you cannot figure it out, use 0000-00-00' mode, though. That flag makes a broken system more broken, and if someone wants more flexibility in storing dates, he could always use char(8) or so.

In the context of this article: if you use your database as a dumb store and put all logic in your application, why would you let MySQL decide for you that, e.g., 2000-12-34 becomes 0000-00-00 and not, for instance, 2001-01-03?