title: Loop over SELECT and insert records postgres
body: <p>I have a mixture of some psuedo code which includes some PostgresSQL. I'd like to do a SELECT and based of this result set I'd like to loop through these results and do a nested loop inside this result set and from that do an <code>INSERT</code>. Any guidance/advice on how I'd go about approaching this would be great:</p>
  
  <pre><code># Old CommissionExpenses do not have the cost_item_id set
  # New CommissionExpenses have the cost_item_id and purchase_id set
  
  # Find _new_ commissions
  results = SELECT * FROM nok.commission_expenses ce WHERE ce.cost_item_id IS NOT NULL AND ce.purchase_id IS NOT NULL
  
  # Loop through those and look up transactions
  
  for result in results
    transactions = SELECT * FROM transactions t WHERE t.target_id::integer = result.purchase_id  
  
    for t in transactions
      INSERT INTO transactions
        nextval('transactions_id_seq'::regclass) as id,
        t.user_id,
        t.transaction_type,
        t.account,
        result.amount as amount,
        result.id as target_id,
        t.target_type,
        t.created_at,
        t.updated_at,
        t.log_id
      ;
  </code></pre>
  
  <p>Syntactically I know this is wrong, but I just thought to highlight the above to express what I'm trying to achieve at a high level. I'm aware that Postgres does support <code>FOR</code> loops and did also attempt to do this myself below as well: </p>
  
  <pre><code>CREATE OR REPLACE FUNCTION loop_and_create() 
  
  RETURNS VOID AS $$
  DECLARE
    rec RECORD;
    txt RECORD;
  BEGIN
    FOR rec IN EXECUTE 'SELECT * FROM nok.commission_expenses ce WHERE ce.cost_item_id IS NOT NULL AND ce.purchase_id IS NOT NULL'
      LOOP
        FOR tx IN EXECUTE 'SELECT * FROM transactions t WHERE t.target_id::integer = rec.purchase_id'
          LOOP
            INSERT INTO transactions
              nextval('transactions_id_seq'::regclass) as id,
              tx.user_id,
              tx.transaction_type,
              tx.account,
              rec.amount as amount,
              rec.id as target_id,
              tx.target_type,
              tx.created_at,
              tx.updated_at,
              tx.log_id
            ; 
          END LOOP; 
     END LOOP;
  END;
  $$ LANGUAGE plpgsql;
  </code></pre>