normalize_package

-- 1mL*7之类的，类似计算的无解
CREATE OR REPLACE FUNCTION normalize_package(text) returns double precision as $BODY$
select CASE lower(COALESCE(substring($1, '([muμMKk]?[GLgl])$'::text), 'mg'::text))
    WHEN 'g'::text THEN (1000)::numeric
    WHEN 'mg'::text THEN (1)::numeric
    WHEN 'ml'::text THEN (1000)::numeric
    WHEN 'l'::text THEN (1000000)::numeric
    WHEN 'kg'::text THEN (1000000)::numeric
    WHEN 'ug'::text THEN 0.001
    WHEN 'μg'::text THEN 0.001
    WHEN 'μl'::text THEN (1)::numeric
    WHEN 'ul'::text THEN (1)::numeric
    ELSE NULL::numeric
    END * COALESCE((substring($1, '(\d+(\.\d+)?)\s?[muμMKk]?[GLgl]'::text))::double precision, NULL::double precision);

        $BODY$
  LANGUAGE sql IMMUTABLE STRICT
  COST 100