Re: Using regoper type with OPERATOR()

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
/**/;/**/

In response to

Responses

Browse pgsql-novice by date

  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()