Re: How-to question: pre-parsing and pre-planning dynamic sql statements

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