Let’s suppose one is tasked with a complex set of calculations where the input data loads from a database and the output data saves to a database. This has come up a number of times in my consulting work. There are 2 approaches:
- Use language of choice, read data, perform calculations, write data
- Work entirely within the database
This latter has the obvious benefits of skipping the data transfer times. This is also where it’s easy to use the first approach, only to run into problems as the amount of data is increased.
Now for simpler tasks I’ve often chosen approach 2. A simpler task, in this case, is when the computation is question can be expressed as an INSERT or UPDATE query. This works when one needs to produce statistics like mean and standard deviation that can be easily expressed as aggregates on the input data. And when something is more complicated than that I have chosen option 1.
One issue with more complicated programming within a database is that there is no agreed upon common portable language. I have client whose databases include MariaDB and SQL Server and thus there is no way to develop code that will work on both of those platforms. I was thinking the other day, wouldn’t it be great if there was an agreed upon embedded language, in addition to whatever legacy languages are support(PL/SQL, T-SQL). Maybe Lua would be a good choice. It’s already available to Postgresql as pllua-ng. Perhaps Python would be good as well.
MariaDB can understand a subset of PL/SQL and (currently small) T-SQL which is nice. Postgresql can understand a subset of PL/SQL. Although I don’t expect Oracle or SQL Server to make changes in this regard, it would be nice if a team created a language that could be added to both that could be used to write portable procedural code.