From: | Gavin Flower <GavinFlower(at)archidevsys(dot)co(dot)nz> |
---|---|
To: | Tony Theodore <tony(dot)theodore(at)gmail(dot)com> |
Cc: | pgsql-novice(at)postgresql(dot)org |
Subject: | Re: Using regoper type with OPERATOR() |
Date: | 2011-10-06 01:43:30 |
Message-ID: | 4E8D07C2.70708@archidevsys.co.nz |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
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
/**/;/**/
From | Date | Subject | |
---|---|---|---|
Next Message | Tony Theodore | 2011-10-06 07:36:03 | Re: Using regoper type with OPERATOR() |
Previous Message | Tony Theodore | 2011-10-05 05:42:18 | Using regoper type with OPERATOR() |