title: Postgres Recursion across 2 alternating fields
body: <p>Here's what I'm trying to do. I have a simple table:</p>
  
  <pre><code>table USAGE_LOG:
  
  USAGE_ID
  PC_ID
  MEMORY_STICK_ID
  </code></pre>
  
  <p>So each usage entry represents a PC being used with a specific memory stick.</p>
  
  <p>Both PC and memory stick ID link to a device table where device_type is 1 for PC and 2 for memory sticks.</p>
  
  <p>My scenario is that I have identified a virus on some PC's, and I now need to trace all possible infected machines using my USAGE_LOG table. </p>
  
  <p>My PC's are marked as having a virus in a separate table:</p>
  
  <pre><code>table VIRUS:
  
  PC_ID
  VIRUS_ID
  </code></pre>
  
  <p>I need to create a CTE (or view) that holds a list of all possibly infected device by recursively searching for each memory stick connected to my infected PC, I then need to find each PC connected to each of my suspect memory sticks, and subsequently each PC etc. etc.</p>
  
  <p>The restrictions are that I cannot change my data structure, and I can't create any functions or stored procedures.</p>
  
  <p>Is this possible, and if so how is it done?</p>