On 6 October 2011 18:36, Tony Theodore <tony(dot)theodore(at)gmail(dot)com> wrote:
> On 6 October 2011 12:43, Gavin Flower <GavinFlower(at)archidevsys(dot)co(dot)nz> wrote:
>> On 05/10/11 18:42, Tony Theodore wrote:
>>> so I could use a query like:
>>> SELECT price OPERATOR(disc_oper::regoper) disc AS disc_amount FROM
>>> This doesn't work however, and I'm not sure why. I think I'm missing
>>> something simple since:
>> I suugests:
>> (1) using the 'money' type instead of float
>> (2) using an enum instedd of regoper
>> WHEN d.type = 'amount'::discount_type THEN i.price - d.amount
>> WHEN d.type = 'fraction'::discount_type THEN i.price * d.fraction
>> ELSE i.price
>> END AS "displayed price"
>> item i LEFT JOIN discount d ON (i.discount_id = d.id)
>> ORDER BY
> Hi Gavin, thanks for the suggestion - after thinking about it some
> more, what I'm actually trying to do is avoid predefined CASE
> statements (and enums). More generally, I'm looking for a general way
> to do function/operator lookups so it's possible to specify/modify the
> logic of certain calculations easily.
> I found the "Executing Dynamic Commands" docs and a function such as:
> CREATE OR REPLACE FUNCTION var_op(left_ double precision, right_
> double precision, operator_ text)
> RETURNS double precision AS
> DECLARE result double precision;
> EXECUTE 'SELECT $1 OPERATOR(' || operator_::regoperator::regoper || ') $2'
> INTO result
> USING left_, right_;
> RETURN result;
> LANGUAGE plpgsql;
> will achieve the result I'm after - but I'm not sure if this is a good idea.
Actually, it performs very poorly - I'll go with the CASE statement.
In response to
pgsql-novice by date
|Next:||From: Henry Drexler||Date: 2011-10-06 19:42:31|
|Subject: opposite of chr(int)|
|Previous:||From: Tony Theodore||Date: 2011-10-06 07:36:03|
|Subject: Re: Using regoper type with OPERATOR()|