I am looking to convert user defined machine readable string formulas into a set of queries and calculations, the result of which would then be saved in another non-normal table. The calcs would be executed via a trigger and a generalized/dynamic/flexible calculation function(s) that would execute when a certain table (bbg_pulls in the simple example below) is updated or inserted on.
For example, if the string passed into the calculation function was:
Where [bp#] designates the table (ex: bp = bbg_pulls) and id and [+/-#] designate the period lag (day lag in my example) relative to
now() for ID just to the left of it.
Then the query would be something like:
select val1/val2 as fin_calc
(select val from bbg_pulls
id = 2
and val_date = (select (CURRENT_TIMESTAMP - INTERVAL '1 day')::date)) as val1
,(select val from bbg_pulls
id = 5
and val_date = (select CURRENT_TIMESTAMP::date)) as val2
(Note that all of the above made up and I have no way to test the queries so they may be syntactically off etc. Hopefully you understand my point though.)
My example above only includes division of two items in the same table, but other strings could be much more complicated, and I would need to incorporate things like percentile rank, the basic mathematically operates, absolute value, etc.
We use PostgreSQL 9.3.5. The queries above are dramatically over simplified for clarity.
What is the correct approach here? Warehouse/MDX/cube solution? Or is there something like this out there already?
My current approach/thinking is to go through all of the various calculations we use and define a separate query etc. in psql for each, create some logic to find out which type of query/function is desired and pass in the IDs as parameters to the specific query/function that was identified. In other words, not a generalized calculation generation function that can take any string calculation, but a set predefined functions and queries that all have to be defined separately and identified in the string formula that is passed in. One would exist for each minor difference in calculation, and we have a lot.
If this question is not answerable, please let me know and I'll take it down.