title: Combining INSERT statements in a data-modifying CTE with a CASE expression
body: <p>My question is some kind of extension to Erwin Brandstetter's excellent answer in <a href="https://stackoverflow.com/questions/8721503/in-postgres-sql-guidance-on-using-the-with-clause">this thread</a> on the correct use of <code>WITH</code>.</p>
  
  <p>My old query looks like this:</p>
  
  <pre><code>WITH x AS (
      INSERT INTO d (dm_id)
      SELECT dm_id
      FROM dm, import i
      WHERE  dm.dm_name = i.dm_name
      RETURNING d_id
  ), y AS (
      INSERT INTO z (d_id)
      SELECT d_id
      FROM x
      RETURNING z_id
  )
  INSERT INTO port (z_id)
  SELECT z_id
  FROM y;
  </code></pre>
  
  <p>This works like a charm. But now, another table (<code>r</code>) has been added (same structure as table <code>d</code>) and with it the possibility that either <code>d_id</code> or <code>r_id</code> has to be added to table <code>z</code>. This, depending on whether <code>dm_name</code> or <code>rm_name</code> is empty in table <code>import</code>. So my theoretical approach would be something like this:</p>
  
  <pre><code>SELECT dm_name, rm_name
  
      ,CASE WHEN dm_name != '' THEN 
          WITH x AS (
              INSERT INTO d (dm_id)
              SELECT dm_id
              FROM dm, import i
              WHERE  dm.dm_name = i.dm_name
              RETURNING d_id
          ), y AS (
              INSERT INTO z (d_id)
              SELECT d_id
              FROM x
              RETURNING z_id
          )
          INSERT INTO port (z_id)
          SELECT z_id
          FROM y
      END
  
      ,CASE WHEN rm_name != '' THEN 
          WITH x AS (
              INSERT INTO r (rm_id)
              SELECT rm_id
              FROM rm, import i
              WHERE  rm.rm_name = i.rm_name
              RETURNING r_id
          ), y AS (
              INSERT INTO z (r_id)
              SELECT r_id
              FROM x
              RETURNING z_id
          )
          INSERT INTO port (z_id)
          SELECT z_id
          FROM y
      END
  
  FROM import;
  </code></pre>
  
  <p>But PostgreSQL tells me:</p>
  
  <blockquote>
    <p>syntax error at or near "INSERT INTO port (z_id)"</p>
  </blockquote>
  
  <p>although that part of the query should be correct as it works already.<br>
  I hope you can help me solve this. :)</p>
  
  <p>For a better understanding - here's the table structure:</p>
  
  <pre><code>CREATE TABLE import (
      dm_name character varying,
      rm_name character varying
      -- many other columns which are not relevant
  );
  
  CREATE TABLE dm (
      dm_id integer NOT NULL, -- serial
      dm_name character varying
      -- plus more columns
  );
  
  CREATE TABLE d (
      d_id integer NOT NULL, -- serial
      dm_id integer -- references dm.dm_id
      -- plus more columns
  );
  
  CREATE TABLE rm (
      rm_id integer NOT NULL, -- serial
      rm_name character varying
      -- plus more columns
  );
  
  CREATE TABLE r (
      r_id integer NOT NULL, -- serial
      rm_id integer -- references rm.rm_id
      -- plus more columns
  );
  
  CREATE TABLE z (
      z_id integer NOT NULL, -- serial
      r_id integer, -- references r.r_id
      d_id integer -- references d.d_id
      -- plus more columns
  );
  
  CREATE TABLE port (
      p_id integer NOT NULL, -- serial
      z_id integer, -- references z.z_id
      -- plus more columns
  );
  </code></pre>
  
  <p>The import table doesn't know the ids as they are generated during the atomization process. The dm and rm tables are for device models which were already extracted from the import table. The d and r tables are for the actual devices. As a port only can only have either a r-device or a d-device or none, the z-table was introduced to have only one field in the port-table representing all possibilities. The d/r and dm/rm tables can't be combined as they have different special columns depending on the device types.</p>