--
-- These are two (of many) routines for a food blog.
--
-- Note RETURNS TABLE as opposed to RETURNS REFCURSOR and their useage.
--

-- 
-- Gets ingredients for recipe by recipe id
--
create or replace function get_recipe_ingredientslist_byID(text) 
returns table (iid        text, 
               recipename text,
               parid      text, 
               wayprep    text,
               quantity   text)
as 
$$
declare
   recipeID text := $1;
begin
   return query
      select ingredients.iid, 
             ingredients.name, 
             ingredientslist.parid, 
             ingredientslist.wayprep, 
             ingredientslist.quantity
      from Ingredients, 
           Ingredientslist,         
           partslist
      where Ingredients.iid = Ingredientslist.iid 
        and partslist.parid = ingredientslist.parid
        and partslist.rid = recipeID;
end;
$$ 
language plpgsql;

select * FROM get_recipe_ingredientslist_byId('R01');


--
-- Get recipe ingredients list by recipr name OR recipe id
--
create or replace function get_recipe_ingredientslist_byNameOrId(text, text, REFCURSOR) returns refcursor as 
$$
declare
   recipeName text      := $1;
   recipeId   text      := $2;
   resultset  REFCURSOR := $3;
begin
   if (recipeId IS NOT NULL) then
      open resultset for 
         select * 
         FROM get_recipe_ingredientslist_byId(recipeId);
   end if;  
   return resultset;
end;
$$ 
language plpgsql;

select get_recipe_ingredientslist_byNameOrId('This is not it', 'R01', 'results');
fetch all from results;