Thread: Using regoper type with OPERATOR()
Hello,
Say I have a discount table that stores either percentages or dollar
amounts, and I'd like to save the operator to be used in a
calculation. I started with a text field and CASE statement, but then
found the operator types that seem more useful:
CREATE TABLE discounts(price float, disc float, disc_oper regoperator);
INSERT INTO discounts VALUES
(100, .1, '*(float, float)'),
(100, 10, '-(float, float)');
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:
SELECT disc_oper::regoper FROM discounts;
disc_oper
--------------
pg_catalog.*
pg_catalog.-
(2 rows)
and
SELECT 100 OPERATOR(pg_catalog.*) .1;
make me think I'm very close.
Any help appreciated.
Thanks,
Tony
On 6 October 2011 12:43, Gavin Flower <GavinFlower@archidevsys.co.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
On 05/10/11 18:42, Tony Theodore wrote:
> Hello,
>
> Say I have a discount table that stores either percentages or dollar
> amounts, and I'd like to save the operator to be used in a
> calculation. I started with a text field and CASE statement, but then
> found the operator types that seem more useful:
>
> CREATE TABLE discounts(price float, disc float, disc_oper regoperator);
> INSERT INTO discounts VALUES
> (100, .1, '*(float, float)'),
> (100, 10, '-(float, float)');
>
> 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:
>
> SELECT disc_oper::regoper FROM discounts;
> disc_oper
> --------------
> pg_catalog.*
> pg_catalog.-
> (2 rows)
>
> and
>
> SELECT 100 OPERATOR(pg_catalog.*) .1;
>
> make me think I'm very close.
>
> Any help appreciated.
>
> Thanks,
>
> Tony
>
I suugests:
(1) using the 'money' type instead of float
(2) using an enum instedd of regoper
A working example of a design that use this follows:
TABLE IF EXISTS item;
DROP TABLE IF EXISTS discount;
DROP TYPE IF EXISTS discount_type;
CREATE TYPE discount_type AS ENUM
(
'amount',
'fraction'
);
CREATE TABLE discount
(
id int PRIMARY KEY,
type discount_type NOT NULL,
amount money,
fraction float,
CHECK
(
(type = 'amount'::discount_type AND amount NOTNULL AND fraction
ISNULL)
OR
(type = 'fraction'::discount_type AND amount ISNULL AND
fraction NOTNULL)
)
);
INSERT INTO discount (id, type, amount, fraction) VALUES
(1, 'amount', 40, NULL),
(2, 'fraction', NULL, 0.15);
CREATE TABLE item
(
id int PRIMARY KEY,
price money NOT NULL,
discount_id int references discount (id),
name text
);
INSERT INTO item (id, price, discount_id, name) VALUES
(1, 100, 1, 'red coat'),
(2, 500, 1, 'gold coat'),
(3, 1000, 2, 'computer'),
(4, 666, NULL, 'Linux 3.5 future eddition');
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
/**/;/**/
On 6 October 2011 18:36, Tony Theodore <tony.theodore@gmail.com> wrote:
> On 6 October 2011 12:43, Gavin Flower <GavinFlower@archidevsys.co.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.
Actually, it performs very poorly - I'll go with the CASE statement.
Cheers,
Tony
On 7 October 2011 06:33, Gavin Flower <GavinFlower@archidevsys.co.nz> wrote: > > Glad to be of help! > > There is often a tradeoff between flexibility and performance. > > What you tried to do looks pretty neat. > > Would writing something in C give you sufficient flexibility with reasonable > performance? Possibly, but I wouldn't know where to start. I just did some more testing, and the most performant solution is to just have both columns (fraction and amount) default them to 1 and 0 respectively, then just calculate (price * fraction + amount). > However, in a production system, and in an environment where most people do > not have a range of skills in depth, it is better to keep things simple - to > ease ongoing maintenance. Sometimes super smart code is a liability, as > mere mortals can not maintain it. I have been guilty of this crime! > > I guess a good rule of thumb, is imagine that you are called back in 2 years > to fix, or modify your code - how would you feel: still proud of what you > did, or wonder what you were thinking at the time (or both!)? > > Somes a bit of complexity is necessar, and can save a lot of code, or imply > be the most practical way of doing something. I was trying to build some flexibility in so that I wouldn't need to revisit this in the future :) Down the track, I'll investigate operator/function lookups further, but I'll keep it simple for the time being. BTW, is novice the right list for questions like these? > Note that one of the points I was trying to make is to avoid float type data > types for money. In COBOL we used integers to hold the number of cents, so > add&subtract operations were not subject to rounding, in pg you can use the > money type. Thanks for the tip, this is mostly an analysis database, so rounding won't be an issue. Cheers, Tony
On 08/10/11 19:21, Tony Theodore wrote:
Cheers,
Gavin
I think this is a better aproach it provides greater flexibility and eliminates the case statement - so I suspect it will be slightly faster.On 7 October 2011 06:33, Gavin Flower <GavinFlower@archidevsys.co.nz> wrote:Glad to be of help! There is often a tradeoff between flexibility and performance. What you tried to do looks pretty neat. Would writing something in C give you sufficient flexibility with reasonable performance?Possibly, but I wouldn't know where to start. I just did some more testing, and the most performant solution is to just have both columns (fraction and amount) default them to 1 and 0 respectively, then just calculate (price * fraction + amount).
However, in a production system, and in an environment where most people do not have a range of skills in depth, it is better to keep things simple - to ease ongoing maintenance. Sometimes super smart code is a liability, as mere mortals can not maintain it. I have been guilty of this crime! I guess a good rule of thumb, is imagine that you are called back in 2 years to fix, or modify your code - how would you feel: still proud of what you did, or wonder what you were thinking at the time (or both!)? Somes a bit of complexity is necessar, and can save a lot of code, or imply be the most practical way of doing something.I was trying to build some flexibility in so that I wouldn't need to revisit this in the future :) Down the track, I'll investigate operator/function lookups further, but I'll keep it simple for the time being. BTW, is novice the right list for questions like these?
I think so, but the pgsql-sql list would probably not be appropriate as you are not asking a trivial question - on balance, I feel this list is best. IMHO How is that for a definitive answer! :-)
I first started useing databases about 20 years ago, and came across pg about 10 years ago. I find reading the pg mailing lists very useful for learning new things. Sometimes I solve problems better than others, but I also often find other people's answers provide more practically elegant ways of doing things than I could have come up with. Other times I've found my understanding not as as good as I had thought - like not appreciating the need to use timestamps with timezone (timestamptz is a pg short form). Having lots of experience is great, but things keep changing and it is important not to get complacent!
You're okay then. Float is probably slightly nore efficient than the money type. Though the money type is probably better from the semantic point of view.Note that one of the points I was trying to make is to avoid float type data types for money. In COBOL we used integers to hold the number of cents, so add&subtract operations were not subject to rounding, in pg you can use the money type.Thanks for the tip, this is mostly an analysis database, so rounding won't be an issue. Cheers, Tony
Cheers,
Gavin
On 10/10/11 10:03, Gavin Flower wrote:
On 08/10/11 19:21, Tony Theodore wrote:[...]On 7 October 2011 06:33, Gavin Flower <GavinFlower@archidevsys.co.nz> wrote:
BTW, is novice the right list for questions like these?
I think so, but the pgsql-sql list would probably not be appropriate as you are not asking a trivial question - on balance, I feel this list is best. IMHO How is that for a definitive answer! :-) I meant to say "... not be inappropriate ... " aaaaarrrrrrghhh!!!!!
[...]
Cheers,
Gavin