title: PostgreSQL insert into multiple tables, using foreign key from first insertion in the second insertion
body: <p>I'm trying to take an existing table and create two. I have a <code>sections</code> table that has a <code>file_url</code>, and now I need to move that into <code>url</code> field on <code>media_files</code> table. Also, I want to populate <code>attachments</code> table that acts as a <code>has_many :through</code> (in Rails) which means it keeps track of the foreign key relationships of <code>media_file_id</code> and <code>section_id</code>.</p>
  
  <p>The closest that I've gotten is this:</p>
  
  <pre><code>WITH ids AS (
      INSERT INTO media_files (url, mimetype)
      SELECT file_url, 'image/jpeg'
      FROM sections
      WHERE file_url IS NOT NULL
      RETURNING id AS media_file_id, sections.id AS section_id
  )
  INSERT INTO attachments (media_file_id, section_id)
  SELECT media_file_id, section_id
  FROM ids
  </code></pre>
  
  <p>but I'm getting this error:</p>
  
  <pre><code>PG::UndefinedTable: ERROR:  missing FROM-clause entry for table "sections"
  LINE 6:         RETURNING id AS media_file_id, sections.id AS sectio...
  </code></pre>
  
  <p>which makes sense, but I'm not sure how to grab the <code>section_id</code>.</p>
  
  <p>Is there a way to get this working? Maybe there's a better, alternative method?</p>
  
  <p><strong>Edit:</strong> Here's a link to <a href="http://sqlfiddle.com/#!15/6fcaf/3" rel="nofollow">SQL Fiddle</a></p>