Dave Jarvis' Repositories

-- Finds ingredients named like an existing category; generates a query for
-- insertion.
select
  rin.label as ingredient,
  rich.label as category,
  rich.hierarchy_label as hierarchy,
  'insert into recipe.ingredient_category (ingredient_category_name_id, ingredient_name_id) values ('||rich.ingredient_category_name_id||','||rin.id||');' as sql 
from
  recipe.ingredient_name_vw rin,
  recipe.ingredient_category_hierarchy_vw rich
where
  rin.label ilike '%'||rich.label||'%' and not exists
  (select id from ingredient_category ic where ic.ingredient_name_id = rin.id and ic.ingredient_category_name_id = rich.ingredient_category_name_id);