Yes, hacking into the catalog is bad. Reason #1 is that if you upgrade to new version and forget to move the hack, things start breaking. Just running pg_dump and loading to the same version on another instance will also lose the hack. There's also always the chance that a new version of Postgres will change so much that your hack is now not possible and force you to go back and re-engineer.
Overriding with your own function is the correct way to go.
This looks ugly:
with uu(unit, coefficient, u_ord) as (
select
unit,
coefficient,
case
when log(u.coefficient) < 0
then floor (log(u.coefficient))
else ceil(log(u.coefficient))
end u_ord
from
unit_conversion u
),
norm (label, norm_qty) as (
select
s.label,
sum( uc.coefficient * s.quantity ) AS norm_qty
from
unit_conversion uc,
substance s
where
uc.unit = s.unit
group by
s.label
),
norm_ord (label, norm_qty, log, ord) as (
select
label,
norm_qty,
log(t.norm_qty) as log,
case
when log(t.norm_qty) < 0
then floor(log(t.norm_qty))
else ceil(log(t.norm_qty))
end ord
from norm t
)
select
norm_ord.label,
norm_ord.norm_qty,
norm_ord.norm_qty / uu.coefficient val,
uu.unit
from
norm_ord,
uu where uu.u_ord =
(select max(uu.u_ord)
from uu
where mod(norm_ord.norm_qty , uu.coefficient) = 0);
but seems to do the trick:
| LABEL | NORM_QTY | VAL | UNIT |
-----------------------------------------
| mercury | 1.5e-8 | 15 | microlitre |
| water | 0.00112 | 112 | centilitre |
You don't really need the parent-child relationship in the unit_conversion
table, because the units in the same family are naturally related to each other by the order of coefficient
, as long as you have the family identified.
Best Answer
More details in the manual: http://www.postgresql.org/docs/current/static/sql-altertable.html