From: | Merlin Moncure <mmoncure(at)gmail(dot)com> |
---|---|
To: | "Davor J(dot)" <davorj(at)live(dot)com> |
Cc: | PostgreSQL General <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: How-to question: pre-parsing and pre-planning dynamic sql statements |
Date: | 2010-08-11 16:24:35 |
Message-ID: | AANLkTindX4ygBsBGU+OVcTwg2OFQ1-XXQkjvQM8kU1nx@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2010-08-11 16:31:51 | Re: InitDB: Bad system call |
Previous Message | Richard | 2010-08-11 16:19:39 | [Some body help me, please!]Re: Re: Restore from online backup, query from pg_proc, got wrong result, please see the problem! |