Postgres Function: Loop through Records and Return Single Value

Question

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

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?

Stefanov.sm · Accepted Answer · 2021-08-06 09:15:37Z

1

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.

edited Aug 6, 2021 at 9:15

answered Aug 6, 2021 at 6:34

Stefanov.sm

13.2k2 gold badges24 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

CodingBrah Over a year ago

Really appreciate the help and insight

Collectives™ on Stack Overflow

Postgres Function: Loop through Records and Return Single Value

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related