Обсуждение: How-to question: pre-parsing and pre-planning dynamic sql statements
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
On Wed, Aug 11, 2010 at 8:35 AM, Davor J. <DavorJ@live.com> wrote: > 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). why do you need to put the formula into a table...how about making functions? merlin
On Wed, Aug 11, 2010 at 11:57 AM, Davor J. <davorj@live.com> wrote: > On 11/08/2010 16:26, Merlin Moncure wrote: >> >> On Wed, Aug 11, 2010 at 8:35 AM, Davor J.<DavorJ@live.com> wrote: >> >>> >>> 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). >>> >> >> why do you need to put the formula into a table...how about making >> functions? >> >> merlin >> >> > > Thanks for the suggestions Merlin. > > There are several reasons: > - it would be very ugly: I cannot enforce referential integrity on > postgres objects, therefore, I cannot "extend" them with attributes > properly. By putting them into a table I can do just that. can you give me an example of how you would need to extend a function with attributes? > - for each data (real/double) the function to be executed should be > determined by a subquery. AFAIS, this would require executing functions > dynamically again whereas the name of the function should be determined by a > subquery. it might be faster to right a small hook in C. For example, you may want to look at: OidFunctionCall1(Oid functionId, Datum arg1) and place your algorithms in hopefully immutable functions. this might be faster than injecting raw expression into dynamic sql (it's definitely cleaner IMO), but I can't guarantee it would be faster than execute '$1 + $1' using somevar; The _fastest_ way is probably going to involve a giant CASE statement :-). merlin