34 lines
693 B
PL/PgSQL
34 lines
693 B
PL/PgSQL
CREATE OR REPLACE FUNCTION convert_weight(
|
|
p_value numeric,
|
|
p_from_unit text,
|
|
p_to_unit text
|
|
)
|
|
RETURNS numeric
|
|
LANGUAGE plpgsql
|
|
AS $$
|
|
DECLARE
|
|
v_from_factor numeric;
|
|
v_to_factor numeric;
|
|
BEGIN
|
|
SELECT factor INTO v_from_factor
|
|
FROM product_uom
|
|
WHERE lower(name) = lower(p_from_unit);
|
|
|
|
IF v_from_factor IS NULL THEN
|
|
RAISE EXCEPTION 'Unknown source unit: %', p_from_unit;
|
|
END IF;
|
|
|
|
SELECT factor INTO v_to_factor
|
|
FROM product_uom
|
|
WHERE lower(name) = lower(p_to_unit);
|
|
|
|
IF v_to_factor IS NULL THEN
|
|
RAISE EXCEPTION 'Unknown target unit: %', p_to_unit;
|
|
END IF;
|
|
|
|
RETURN p_value * v_from_factor / v_to_factor;
|
|
END;
|
|
$$;
|
|
|
|
|