PostgreSQL, consulta compleja para calcular los ingredientes por receta
Tengo que calcular los alimentos e ingredientes de los alimentos usados almacenados en las tablas de PostgreSQL:
table1 'usedfood'
food food used used
code name qty meas
----------------------------------------------
10 spaghetti 3 pcs
156 mayonnaise 2 pcs
173 ketchup 1 pcs
172 bolognese sauce 2 pcs
173 ketchup 1 pcs
10 spaghetti 2 pcs
156 mayonnaise 1 pcs
table2 'ingredients'
food ingr. ingredient qty meas
code code name /1 in 1
----------------------------------------------
10 1256 spaghetti rinf 75 gramm
156 1144 salt 0.3 gramm
10 1144 salt 0.5 gramm
156 1140 fresh egg 50 gramm
172 1138 tomato 80 gramm
156 1139 mustard 5 gramm
172 1136 clove 1 gramm
156 1258 oil 120 gramm
172 1135 laurel 0.4 gramm
10 1258 oil 0.4 gramm
172 1130 corned beef 40 gramm
resultado:
used
times code food/ingredient qty meas
----------------------------------------------
5 1256 spaghetti rinf 375 gramm
8 1258 oil 362 gramm
2 1138 tomato 160 gramm
3 1140 fresh egg 150 gramm
2 1130 corned beef 80 gramm
3 1139 mustard 15 gramm
8 1144 salt 3.4 gramm
2 1136 clove 2 gramm
2 1135 laurel 0.8 gramm
2 173 ketchup 2 pcs //haven't any ingredients
Por ahora, hago esto haciendo un bucle a través de la tabla 1 y consultando la tabla 2 para cada fila, luego agregando resultados y así sucesivamente (con C) lo que puede ser muy lento en datos más grandes.
La tabla 1 contiene el código de los alimentos, el nombre de los alimentos y la cantidad utilizada. La Tabla 2 contiene ingredientes (en orden desordenado) con el código y la cantidad utilizada para una sola comida y también el código de comida en el que aparece.
La cantidad usada de la tabla 1 debe multiplicarse por la cantidad de la tabla 2 de acuerdo con cada receta y debe agregarse al resultado del código de ingredientes.
Así que todas las filas de ingredientes que van a la comida "espaguetis" comienzan con el código de espaguetis (10).
Los alimentos sin ningún ingrediente deben calcularse con la cantidad de la tabla 1 y mostrarse con el mismo nombre. Eso significa que es un producto final (como una botella de cerveza).
Aquí puede haber más complicaciones, pero tengo miedo de preguntar. Por ejemplo, en la lista de ingredientes puede ser un ingrediente que es una receta por sí mismo. Por ejemplo, la mostaza que contiene vinagre, sal, semillas, etc ... ¿Entonces qué? En la tabla 2 del ejemplo mostrado, la mostaza se usa como producto listo (componente).
¿Hay alguna forma de hacer tal cálculo y obtener resultados rápidamente con solo PostgreSQL que dará resultados listos para el programa C?
Tal vez no sea tan complejo como parece para mí? ¿Cómo sería esa consulta?