Re: Using regoper type with OPERATOR() - Mailing list 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
/**/;/**/