Re: 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(at)live(dot)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(at)live(dot)com> wrote:
> On 11/08/2010 16:26, Merlin Moncure wrote:
>>
>> On Wed, Aug 11, 2010 at 8:35 AM, Davor J.<DavorJ(at)live(dot)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