CODEWITHSUNDEEP
postgresqlplpgsql
CodingBrah
CodingBrah

Reputation: 69

Postgres Function: Loop through Records and Return Single Value

New to Postgres and trying to get a function to get how many recipes I can make based on available ingredients.

recipe_id required_ingredient_quantity ingredient_id ingredient_quantity
123 10.00 555 21.00
123 3.00 666 15.00
123 4.00 777 11.00 
CREATE OR REPLACE FUNCTION fn_limiting_part(recipe_id VARCHAR) 
RETURNS INT AS
$$
   DECLARE
      limiting_ingredient INT;
      limiting_ingredient NUMERIC(6,2)[];
   BEGIN
   FOR record IN
      SELECT
        recipes_ingredients.recipe_id,
        recipes_ingredients.required_ingredient_quantity,
        
        ingredients.ingredient_id,
        ingredients.ingredient_quantity
      FROM ingredients
      INNER JOIN recipes_ingredients
      ON ingredients.ingredient_id = recipes_ingredients.ingredient_id
      INTO 
      WHERE recipes_ingredients.recipe_id = fn_limiting_part.recipe_id;
   LOOP
      array_append(limiting_ingredient, ingredient_quantity * (1 / required_ingredient_quantity))
      -- Find lowest limiting ingredient in limiting_ingredients array
      -- Assign lowest amount to limiting_part variable
      -- Use Floor to Round down to nearest whole number
   END LOOP;
        limiting_part := FLOOR();

    
   RETURNS limiting_part
   END;
$$
LANGUAGE PLPGSQL;

Other Questions/Considerations

  1. Is this a good practice to run this type of logic in postgresql this is going to be part of a GraphQL API I am making so it would be nice to return just an INT or should I make separate GraphQL queries in do this logic in my frontend app?

Upvotes: 0

Views: 761

Answers (1)

Stefanov.sm
Stefanov.sm

Reputation: 13049

You do not really need a plpgsql function. Plain SQL, MIN and filtering by recipe_id will be enough.

create function fn_limiting_part(arg_recipe_id int) returns int as
$$
SELECT MIN(floor(ingredient_quantity/required_ingredient_quantity))::int
FROM ingredients
 INNER JOIN recipes_ingredients
 ON ingredients.ingredient_id = recipes_ingredients.ingredient_id
WHERE recipe_id = arg_recipe_id;
$$ language sql;

I think that it is a very good practice to have as much data-related or domain-related logic as possible done in the database. This brings large benefits in performance, concurrency and traffic reduction among others.

Upvotes: 1

Related Questions