Annotation: core.convert_unit

Information: core.convert_unit

Implementation: core.convert_unit

CREATE OR REPLACE FUNCTION core.convert_unit(from_unit integer, to_unit integer)
 RETURNS numeric
 LANGUAGE plpgsql
 STABLE
AS $function$
    DECLARE _factor decimal;
BEGIN
    IF(core.get_root_unit_id($1) != core.get_root_unit_id($2)) THEN
        RETURN 0;
    END IF;

    IF($1 = $2) THEN
        RETURN 1.00;
    END IF;
    
    IF(core.is_parent_unit($1, $2)) THEN
            WITH RECURSIVE unit_cte(unit_id, value) AS 
            (
                SELECT tn.compare_unit_id, tn.value
                FROM core.compound_units AS tn WHERE tn.base_unit_id = $1

                UNION ALL

                SELECT 
                c.compare_unit_id, c.value * p.value
                FROM unit_cte AS p, 
                core.compound_units AS c 
                WHERE base_unit_id = p.unit_id
            )
        SELECT 1.00/value INTO _factor
        FROM unit_cte
        WHERE unit_id=$2;
    ELSE
            WITH RECURSIVE unit_cte(unit_id, value) AS 
            (
             SELECT tn.compare_unit_id, tn.value
                FROM core.compound_units AS tn WHERE tn.base_unit_id = $2
            UNION ALL
             SELECT 
                c.compare_unit_id, c.value * p.value
                FROM unit_cte AS p, 
              core.compound_units AS c 
                WHERE base_unit_id = p.unit_id
            )

        SELECT value INTO _factor
        FROM unit_cte
        WHERE unit_id=$1;
    END IF;

    RETURN _factor;
END
$function$