Suppose you have a table CREATE TABLE tbl_formulas (formula_id integer,
formula text)
The formula field can be any postgres-supported mathematical operation which
references some input data with $1 like "sin($1) + cos($1)" and returns one
numeric value. Such formulas should be used in the SELECT clause and should
be executed quickly for each data (real/double).
Conceptual example:
SELECT put_data_into_specific_formula_and_evaluate(data) FROM tbl_data
WHERE....
Current inefficient solution:
----------------------------
What I have now (and which works well but is extremely slow) is a plpgsql
function object fnc_calc(formula_id integer, data real) which simply looks
at the table tbl_formulas end returns EXECUTE 'SELECT ' || formula USING
data INTO result; As you might expect, this is very slow because constant
parsing and planning of the formula for each data.
Possibly faster but ugly solution:
----------------------------
I have thought about pre-defining SQL-based functions (or even types) based
on these formulas, but this seems very cumbersome and would require
defining, using and deleting these function-objects on each query execution.
The impossibility to define functions as temporary objects makes this
approach even more ugly.
So my question is this:
Has anyone any suggestions how to optimize this through C or (plpg)SQL?
Also, I have tried to find some inspiration by googling, but couldn't find
anything, so if anyone has suggestions for keywords on which to search, that
would be also appreciated.
The ideal solution should be fast and writable in one single SQL statement
such as "SELECT fnc_calc(formula_id , data) FROM tbl_data WHERE.. " so the
calculating function should take 2 (or more) arguments.
Regards,
Davor