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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
