title: DELETE recursive PostgreSQL
body: <p>I have a table <strong>upload_temp</strong> as it follows:</p>
  
  <pre><code>CREATE TABLE upload_temp (
      codigo serial PRIMARY KEY NOT NULL,
      codigo_upload_temp_pai INTEGER,
      nome TEXT NOT NULL,
      codigo_extensao INTEGER,
      data_inclusao TIMESTAMP NOT NULL DEFAULT NOW(),
      codigo_usuario_inclusao INTEGER NOT NULL,
  
      CONSTRAINT fk_upload_upload_pai FOREIGN KEY (codigo_upload_temp_pai) REFERENCES upload_temp (codigo) MATCH SIMPLE ON UPDATE CASCADE ON DELETE CASCADE,
      CONSTRAINT fk_extensao_upload FOREIGN KEY (codigo_extensao) REFERENCES extensao (codigo) MATCH SIMPLE ON UPDATE CASCADE ON DELETE NO ACTION,
      CONSTRAINT fk_usuario_upload FOREIGN KEY (codigo_usuario_inclusao) REFERENCES usuario (chave) MATCH SIMPLE ON UPDATE CASCADE ON DELETE CASCADE,
      CONSTRAINT uq_upload UNIQUE('nome', COALESCE('codigo_extensao', -1), COALESCE('codigo_upload_temp_pai', -1), 'codigo_usuario_inclusao', DATE(data_inclusao))
  );
  </code></pre>
  
  <p>This table stores all temporary uploads of my system, and these uploads can be <strong>folders</strong> and <strong>files</strong>. The self reference feature on the table handles that. But the thing is: when some file is registered to the system, it becomes an official digital file and the <strong>upload_temp</strong> is deleted. When it is deleted, its parent folder must be deleted only if it becomes an empty folder. By this way, I need to delete all folders from this tree as long as they get empty by losing their only child. The following picture contains more details:</p>
  
  <p><a href="https://i.stack.imgur.com/tVshx.png" rel="nofollow noreferrer">Tree of folders and files</a></p>
  
  <p>The file <strong>5.jpg</strong> belongs to <strong>folder 5</strong>, which belongs to <strong>folder 4</strong>, and so on. If I choose the file <strong>5.jpg</strong> to be registered to the system, it will be deleted from <strong>upload_temp</strong>, which will empty <strong>folder 5</strong>. So the <strong>folder 5</strong>, by being empty, must be deleted as well, and the same will happen to all the parent folders in this case.</p>
  
  <p>Although I'm using PHP, I need a solution in PostgreSQL aiming the performance. I'm trying to understand how <code>WITH RECURSIVE</code> works, but I'm having difficulties. I wrote the following code that is supposed to remove all parents recursively, starting from the file <strong>5.jpg</strong>, disregarding the empty feature:</p>
  
  <pre><code>WITH RECURSIVE all_uploads (codigo, parent, ext, main) AS (
     SELECT ut1.codigo, ut1.codigo_upload_temp_pai AS parent, ut1.codigo_extensao AS ext, ut1.codigo AS main
     FROM upload_temp ut1
  
     UNION ALL
  
     SELECT ut2.codigo, ut2.codigo_upload_temp_pai AS parent, ut2.codigo_extensao AS ext, au.main
    FROM upload_temp ut2
     JOIN all_uploads au ON au.parent = ut2.codigo
  )
  DELETE FROM upload_temp WHERE codigo IN (SELECT codigo FROM all_uploads WHERE codigo = 486);
  </code></pre>
  
  <p>The following picture show the the result for <code>SELECT * FROM upload_temp ORDER BY codigo</code>:</p>
  
  <p><a href="https://i.stack.imgur.com/aqKrK.png" rel="nofollow noreferrer">Select result</a></p>
  
  <p>Well, it's not working. It is only deleting one file. What can I do to solve this? Thanks!</p>