I think the irony is that much of the problems of sql are related to a lack of tooling.
The lack of tooling is result of large numbers of devs being distracted by ORM which moves that particular problem into the language ide/editor.
Essentially we lack great *.sql editing, ide, macro, refactoring because of the lack of focus on sql itself.
edit: as an aside, string concatenation in the language of choice is its own road to hell.
Except that my other tools are just an editor like emacs and makefiles. So now to be productive I have to have and ide for sql, learn sql, learn macros.
Interesting that SQL came first. If it was so easy and obvious to use it we would not have seen any ORMs by now. But every other project that uses SQL databases ends up with some kind of ORM.
One of the reason is probably because there is an impedance mismatch. Code is already written dealing with objects, functions, pointers, data. But when it talks to the database it has to talk via a command line string interface so to speak.
There is another type of impedance mismatch and that is learning a new language. One can argue SQL is there to keep & manipulate the most precious things you have -- data, so one should spend time learning it well. But the other point is, SQL is there to keep & manipulate the most precious things you -- data, so don't mess with it if you don't know SQL very well. Let someone else learn it (the author of the ORM).
I am not arguing for one point or the other, just kind saying how it is interesting how we have been going in a circle the last 15 years or so.
I find LINQ + Entity Framework to be a blessing in C. Though I use the monad/function syntax not the LINQ query sugar when I use it.
sql-mode for some databases(mysql and oracle iirc) reads the DDL of the tables you are typing about, why is this not better and more general?
Sql is so well established and general as a solution that not learning it is a poor choice.
Notice that its only a yearish old I think?
The SQL code completion is quite good (and it caches your entire database schema so it is very quick). That's not particularly special on it's own, but what makes this especially useful is IDEA's "language injection"  feature. This allows you to, for example, get completion for SQL when it is contained in some other language, which could be anything (e.g. a Java String, Ruby String, XML, etc). It will also analyze and report errors in this SQL on the fly.
And SQL statements contained in concatenated strings are no longer a problem because you can edit those in a separate editor window where you are only editing the SQL, and it automatically gets placed into the concatenated string.
Not sure if it addresses all of your specific concerns, though.
Pgtap is on my list of things to poke around with.
edit: thanks btw