I've built inventory systems in a similar fashion.
On one hand, using a bunch of PL/pgSQL makes sense for transaction isolation and faster execution. I'm not sure how much of the arguments about business logic matter in this case, but a strong argument for using PL/pgSQL is that these queries written in Python (for example) are going to be significantly slower, and that really matters when there are 100 concurrent users hitting the database every 10 seconds. No one likes waiting for their system to update... they'll just switch over to Excel. I think that PL/pgSQL is a great use-case for situations where preoptimizing for speed isn't a mistake, though the trade off is that you need to find the rare expert on processing languages (what order to triggers fire and how do you prevent this from being an issue?), and who can work through the complex logic of the system.
I wonder why the author didn't use windowing instead the lateral example. You can window and sum over composite rows, and it goes pretty fast. I never did a direct comparison between lateral and windowing, but windowing would be much cleaner and not require you to generate a series.
Despite being pretty familiar with window functions, I couldn't figure out how to do it for that example in the time I was willing to spend on this post :). If you have a better query, I'm happy to update the article and give credit.
On one hand, using a bunch of PL/pgSQL makes sense for transaction isolation and faster execution. I'm not sure how much of the arguments about business logic matter in this case, but a strong argument for using PL/pgSQL is that these queries written in Python (for example) are going to be significantly slower, and that really matters when there are 100 concurrent users hitting the database every 10 seconds. No one likes waiting for their system to update... they'll just switch over to Excel. I think that PL/pgSQL is a great use-case for situations where preoptimizing for speed isn't a mistake, though the trade off is that you need to find the rare expert on processing languages (what order to triggers fire and how do you prevent this from being an issue?), and who can work through the complex logic of the system.
I wonder why the author didn't use windowing instead the lateral example. You can window and sum over composite rows, and it goes pretty fast. I never did a direct comparison between lateral and windowing, but windowing would be much cleaner and not require you to generate a series.