DELETE recursive PostgreSQL

Question

I have a table upload_temp as it follows:

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))
);

This table stores all temporary uploads of my system, and these uploads can be folders and files. 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 upload_temp 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:

Tree of folders and files

The file 5.jpg belongs to folder 5, which belongs to folder 4, and so on. If I choose the file 5.jpg to be registered to the system, it will be deleted from upload_temp, which will empty folder 5. So the folder 5, by being empty, must be deleted as well, and the same will happen to all the parent folders in this case.

Although I'm using PHP, I need a solution in PostgreSQL aiming the performance. I'm trying to understand how WITH RECURSIVE works, but I'm having difficulties. I wrote the following code that is supposed to remove all parents recursively, starting from the file 5.jpg, disregarding the empty feature:

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);

The following picture show the the result for SELECT * FROM upload_temp ORDER BY codigo:

Select result

Well, it's not working. It is only deleting one file. What can I do to solve this? Thanks!

Answer 1

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
 WHERE ut1.codigo = 486

 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);

You have to put the starting point in the initial select (inside the with) OR you have to somehow make a pseudocolumn that represents the top of the "tree", that's the same for every row in that entire tree. Putting the "top where" in the initial select inside the with is the easier solution.