title: PostgreSQL, complex query for calculating ingredients by recipe
body: <p>I have to calculate food and ingredients of used food stored in PostgreSQL tables:</p>
  
  <pre><code>    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
  </code></pre>
  
  <p>result:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>For now I do this by looping through table1 and queriying table2 for each row then adding results and so on (with C) what may be very slow on larger data.   </p>
  
  <p>Table1 contains food code, food name and used quantity.
  Table2 contains ingredients (in messy order) with code and used quantity for one peace of food and also code of food in which appears.<br>
  Used quantity from table1 should be multiplied with quantity from table2 according to every recipe and should be added to result of ingredients code.<br>
  So all ingredient rows which goes to food "spaghetti" starts with food spaghetti code (10).<br>
  Food without any ingredient should be calculated with quantity from table1 and showed with same name. That actually mean it is a final product (like beer bottle).<br>
  Here may be more complication but I'm, affraid to ask.
  For example in ingredinets list may be ingredient which is recipe by himself. For example mustard which contains from vinegar, salt, seed, etc... What then?
  In table2 of showed example mustard is used as ready product (component).  </p>
  
  <p>Is here any way for do such calculation and getting results fast with using just PostgreSQL which will give ready results to C program?<br>
  Maybe not too complex as seem's for me? How would that query look like?</p>