The PLs (Procedural Languages) in PostgreSQL let you write stored procedures (i.e. functions) that can be called on their own or via a trigger (e.g. when a row is inserted or updated).
For instance, if you had a field that you wanted to be the output of some complex expression, algorithm, &c, you can write it as a function. For instance, something like Greatest Common Divisor (GCD) or geometry functions (e.g. intersection of two geometries, the union or intersection of two geometries, &c). Another example is custom aggregate functions.
This just lets you do it in Lua, if that's your thing. I think by default there is PL/pgSQL, PL/Tcl, PL/Perl , and PL/Python. There are plugins for a variety of other languages (e.g. ruby, java lua, R). I actually thought Lua became a default recently, but maybe they're still talking about it.
Yes. PL/pgSQL is the default AFAIK (been away from active use of Postgres for a few years). PL/pgSQL is basically the same as Oracle's PL/SQL, which has a very Pascal-like syntax.
I played around with PL/Lua 5 or 6 years ago, and liked it, but the project/idea got sidelined for other reasons so I never got very deep into it.
There is only one container type: the table. There is only one number type. (Since 5.3 Lua internally uses 2: ints and doubles, but that’s an implementation detail.) etc.
2. Lua is easy to integrate.
3. It’s easy to set up a sandboxed environment with Lua.
4. Lua is surprisingly fast, especially considering it is interpreted and dynamic.
If you take this idea a lot farther - imagine your entire application — frontend, backend, web-service, views — can run as a stored procedure inside the database, and thus load data so fast that it opens up new architectural possibilities beyond client/server. That sounds like crazyland for people from the postgres/rest mindset, but it starts to become possible when you have a simple and portable language that runs in many places. http://www.dustingetz.com/:stu-halloway-datomic-ions-in-seve...
I have been pushing a pet idea that we should leverage our RDBMSes as platforms more than we do. Pretty frequently the model layer in MVCs end up with some functionality that would be much more robust inside the database. Validation ends up being done (at least) twice, query results end up being quite distant from objects, transactions which should be one are split, etc. Problems that could be mitigated with user defined procedures in the database.
I think it might be the current state of version control tooling that's in the way here. It can be a tricky and often fairly manual process to sync database changes (even just running the migrate command) when releases are tied to git repos.
That's not to say git repos aren't wonderful, but I see underutilization of database functionality as a subtle downside.
Stored Proc biz-logic architecture was a big thing in the 2000s. MSSQL with its T-SQL were very common (and its polymorphic AND multi-table return types, could make for some very interesting control flows sometimes).
There was a lot of bitterness about it back in the late 00's and early 10's, and it drove a lot of ORM adoption.
I worked for a shop in the late 00's and early 10's that had Very Big Database Servers doing several hundred million transactions per second (100+ core, multiple RAMSan, etc. type bare metal servers), and stored procs were the way the go. They were basically the interface layer to the data, and it allowed us to keep the interface (or even version it) but change how access happened behind the scenes, like splitting a table into partitions, or replacing a bad performing query with a differently structured one that did better, which made it a non-concern for the frontend code that was responsible for getting data and formatting it, and didn't really care about _how_ that data was retrieved.
reply