grocery-manager/units.sql

BEGIN;
CALL insert_unit ('fl. oz. (US)', 'Volume');
CALL insert_unit ('Pint (US)', 'Volume');
CALL insert_unit ('Tbsp (US)', 'Volume');
CALL insert_unit ('tsp (US)', 'Volume');
CALL insert_unit ('Cup (US)', 'Volume');
CALL insert_unit ('Cup (US Legal)', 'Volume');
CALL insert_unit ('Quart (US)', 'Volume');
CALL insert_unit ('Gallon (US)', 'Volume');
CALL insert_unit ('tsp (metric)', 'Volume');
CALL insert_unit ('Tbsp (metric)', 'Volume');
CALL insert_unit ('Cup (metric)', 'Volume');
SELECT * FROM units;
-- using customary cup as per https://en.wikipedia.org/wiki/Cup_(unit)
CALL insert_unit_conversion ('tsp (metric)', 'mL', 5);
CALL insert_unit_conversion ('Tbsp (metric)', 'mL', 15);
CALL insert_unit_conversion ('Cup (metric)', 'mL', 250);
CALL insert_unit_conversion ('Cup (US)', 'mL', 236.5882365);
CALL insert_unit_conversion ('Cup (US Legal)', 'mL', 240);
CALL insert_unit_conversion ('Cup (US)', 'Tbsp (US)', 16);
CALL insert_unit_conversion ('Cup (US)', 'tsp (US)',  48);
CALL insert_unit_conversion ('Cup (US)', 'fl. oz. (US)', 8);
CALL insert_unit_conversion ('Pint (US)', 'Cup (US)', 2);
CALL insert_unit_conversion ('Quart (US)', 'Cup (US)', 4);
CALL insert_unit_conversion ('Gallon (US)', 'Cup (US)', 16);
CALL insert_unit_conversion ('Cup (metric)', 'kg', 'Whole Oats', 0.214/2);
CALL insert_unit_conversion ('Cup (metric)', 'kg', 'Puffed Rice', .03);
CALL insert_unit_conversion ('Cup (metric)', 'kg', 'Coconut Desiccated', .071*2);
-- CALL insert_unit_conversion ('Cup (metric)', 'kg', 'Peanut butter', .24);
CALL insert_unit_conversion ('Cup (metric)', 'kg', 'Honey', .68);
CALL insert_unit_conversion ('Cup (metric)', 'kg', 'Brown Sugar', .055*4);
CALL insert_unit_conversion ('Cup (metric)', 'kg', 'Red Lentils', .199);
CALL insert_unit_conversion ('L', 'kg', 'Coconut Oil', .95);
CALL insert_unit_conversion ('Cup (metric)', 'kg', 'Brown Rice', .19);
SELECT * FROM conversions;
DO
$$
DECLARE
  item record;
  msg text;
  expected numeric(20,10);
  result numeric(20,10);
BEGIN
  FOR item IN SELECT * FROM (VALUES
      ('L', 'mL', '', 1000), -- *
      ('mL', 'L', '', 1.0/1000),
      ('tsp (metric)', 'mL', '', 5), -- *
      ('Tbsp (metric)', 'mL', '', 15), -- *
      ('Cup (metric)', 'mL', '', 250), -- *
      ('Cup (US)', 'tsp (metric)', '', 236.5882365/5),
      ('Cup (US)', 'Tbsp (metric)', '', 236.5882365/15),
      ('Cup (US Legal)', 'mL', '', 240), -- *
      ('Cup (US Legal)', 'Tbsp (metric)', '', 16),
      ('L', 'Cup (metric)', '', 4),
      ('Cup (US)', 'Cup (metric)', '', 236.5882365/250),
      ('Cup (US)', 'mL', '', 236.5882365),
      ('mL', 'Cup (US)', '', 1/236.5882365), -- *
      ('L', 'Cup (US)', '', 1000/236.5882365),
      ('Cup (US)', 'Tbsp (US)', '', 16), -- *
      ('mL', 'Tbsp (US)', '', 16/236.5882365),
      ('Tbsp (US)', 'tsp (US)', '', 3), -- *
      ('mL', 'tsp (US)', '', 16*3/236.5882365),
      ('Pint (US)', 'fl. oz. (US)', '', 16), -- *
      ('Pint (US)', 'Cup (US)', '', 2), -- *
      ('Cup (US)', 'fl. oz. (US)', '', 8),
      ('fl. oz. (US)', 'Tbsp (US)', '', 16*2/16),
      ('fl. oz. (US)', 'tsp (US)', '', 16*2*3/16),
      ('Quart (US)', 'fl. oz. (US)', '', 32), -- *
      ('fl. oz. (US)', 'Quart (US)', '', 1.0/32),
      ('Gallon (US)', 'fl. oz. (US)', '', 128),
      ('Gallon (US)', 'Pint (US)', '', 8),
      ('Gallon (US)', 'Quart (US)', '', 4), -- *
      ('Gallon (US)', 'mL', '', 4*32*236.5882365/8),
      ('Cup (metric)', 'kg', 'Whole Oats', 0.214/2), -- *
      ('Cup (metric)', 'kg', 'Puffed Rice', .03), -- *
      ('Cup (metric)', 'kg', 'Coconut Desiccated', .071*2), -- *
      -- ('Cup (metric)', 'kg', 'Peanut butter', .24),
      ('Cup (metric)', 'kg', 'Honey', .68), -- *
      ('kg', 'mL', 'Honey', 250/.68),
      ('Cup (metric)', 'kg', 'Brown Sugar', .055*4), -- *
      ('g', 'mL', 'Brown Sugar', 250/.055/4/1000),
      ('Cup (metric)', 'kg', 'Red Lentils', .199), -- *
      ('L', 'kg', 'Coconut Oil', .95), -- *
      ('Cup (metric)', 'g', 'Red Lentils', 199),
      ('mL', 'g', 'Coconut Oil', 950.0/1000),
      ('Cup (metric)', 'kg', 'Brown Rice', .19) -- *
    ) AS tests(f,t,p,e) LOOP
    BEGIN
      SELECT convert_unit(item.f, item.t, item.p) INTO result;
      SELECT item.e INTO expected;
      ASSERT result = expected, 'convert_unit('''||item.f||''', '''||item.t||''', '''||item.p||''') = '||result||' != '||expected;
      RAISE NOTICE ' convert_unit(''%'', ''%'', ''%'') = %', item.f, item.t, item.p, result;
      EXCEPTION
        WHEN assert_failure THEN
        BEGIN
          GET STACKED DIAGNOSTICS msg = MESSAGE_TEXT;
          RAISE WARNING '%', msg;
        END;
    END;
  END LOOP;

END;
$$ LANGUAGE plpgsql;
ROLLBACK;