From: | Tony Theodore <tony(dot)theodore(at)gmail(dot)com> |
---|---|
To: | Gavin Flower <GavinFlower(at)archidevsys(dot)co(dot)nz> |
Cc: | pgsql-novice(at)postgresql(dot)org |
Subject: | Re: Using regoper type with OPERATOR() |
Date: | 2011-10-06 07:36:03 |
Message-ID: | CAJFv53oAT0sRGPPvQiSZssCpxB0yWvmDYDKeA90eLjspKtDZNA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
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
>> discounts
>>
>> 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
>
[...]
>
> SELECT
> i.name,
>
> CASE
> 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"
> FROM
> item i LEFT JOIN discount d ON (i.discount_id = d.id)
> ORDER BY
> i.name
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;
BEGIN
EXECUTE 'SELECT $1 OPERATOR(' || operator_::regoperator::regoper || ') $2'
INTO result
USING left_, right_;
RETURN result;
END;
$$
LANGUAGE plpgsql;
will achieve the result I'm after - but I'm not sure if this is a good idea.
Thanks,
Tony
From | Date | Subject | |
---|---|---|---|
Next Message | Tony Theodore | 2011-10-06 08:49:54 | Re: Using regoper type with OPERATOR() |
Previous Message | Gavin Flower | 2011-10-06 01:43:30 | Re: Using regoper type with OPERATOR() |