Delete Recursive Postgresql

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_

Solution 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.