title: Recursive Insert into clause in Postgresql
body: <p>I want to perform a big insert statement on multiple lines, but  recursion is making it hard to build the correct SQL statement. I believe an example will make it easier to explain. Consider the model:</p>
  
  <p><strong>car</strong></p>
  
  <pre>
  
  |id|code|Model name     |
  |1 |100 |Deluxe         |
  |10|100 |Deluxe improved|
  |2 |200 |Standard       |
  |20|200 |Standard new   |
  </pre>
  
  <p><strong>color</strong></p>
  
  <pre>
  |id|Name|
  |2 |Red |
  |3 |Blue|
  </pre>
  
  <p><strong>car_colors</strong></p>
  
  <pre>
  |id|car_id|color_id|
  |3 |1     |2       |
  |4 |2     |2       |
  |5 |2     |3       |
  </pre>
  
  <p>The deluxe car was added, and afterwards the "deluxe improved" model was inserted. It's a new version of the same car (same code). Unfortunately, John Doe forgot to update the car_colors table, so now you want to update that table by inserting the same colors for every same car code.</p>
  
  <p>In the example considered, we'd like to add the tuple "Deluxe improved, red" (because Deluxe and deluxe improved have the same code and Deluxe is available in red) and the tuples "standard new, red" and "standard new, black" for the same reasons.</p>
  
  <p>The PSEUDO-CODE (non-sql) should be something like:
  all_cars_and_colors = select * from car left outer join car_colors</p>
  
  <pre><code>for each(this_car:all_cars_and_colors){
  
      if(all_cars_and_colors.color_id does not exist){
          car_colors_to_copy = select * from car inner join car_colors where car.code=this_car.code
  
          for each(color_to_copy: car_colors_to_copy){
              insert into car_colors(id,car_id,color_id) VALUES (nextval('id_sequence') ,this_car.id,color_to_copy.color_id)
          }
  
      }
  }
  </code></pre>
  
  <p><strong>How would one solve this using SQL?</strong></p>