But isn't the nightmare a human, collaboration problem rather than a technical one? Assuming that the purpose of these stored procedures is to ensure data integrity at a lower level of abstraction and that you have appropriate staff to maintain that layer, I don't see where the nightmare part comes in.
In my experience, and perhaps with my bias as someone who's very comfortable with (relational) databases, I have seen plenty of nightmarish scenarios involving, say, Hibernate.
PL/SQL is an awful language to work with. What's up with having to declare variables before using them? Does it not even have multi-pass compilation?! And it works only with Oracle, which causes lock-in I'd rather avoid.
I'd rather create a service layer on top of my DB and access it through that. Then I have more control over logging, managing access, pubsub, etc.
It makes me laugh, people who won't write a single keyword to specify the type of a variable, but will write a hundred lines of unit tests to make sure it's always what you want.
Oh I'm sorry, didn't know we were playing word games. The above poster complained about having to declare the types of his variables as if that was a bad thing. I simply pointed why it was in fact a good thing - you don't now need to do tests to ensure that your function that expects an INT always gets an INT, the type system takes care of it for you.
Discussions at this level presuppose a certain amount of background knowledge. Do try to keep up.
Declaring variables isn't hard.
However, in Postgresql one can use many languages to write stored procs. I prefer plpgsl but TCL, Python and Perl are also available in the stock distribution.
"isn't hard"? Yeah, writing it in Algol isn't "hard" either, but why? If something's harder to use and less performant, why would it even be on my list of things to maybe consider?
The maintenance issue is tractable, I'm just saying that PL/SQL doesn't make it easier and probably makes it worse by being hard to unit test, refactor, etc (although this is very subjective and depends on the strengths of your team).
The scalability problem? Good luck getting 90%+ of your processing out of the database. Rewrite from scratch?
In my experience, and perhaps with my bias as someone who's very comfortable with (relational) databases, I have seen plenty of nightmarish scenarios involving, say, Hibernate.