DELETE recursive PostgreSQL respecting specific conditions

Question

A few days ago I asked a question about deleting using WITH RECURSIVE from PostgreSQL. There is:

DELETE recursive PostgreSQL

That works fine: the intention, initially, was to delete parent folders recursively as long as the final child was deleted. The following image describes it better:

Files tree view

By deleting the file 5.jpg, all parent folders, in this situation, would be deleted as well.

But now I have to delete the parent folders only if they get empty, i.e. by losing its only child. I tried the following:

 WITH RECURSIVE all_uploads (codigo, parent, ext, uploader) AS (
    SELECT ut1.codigo, ut1.codigo_upload_temp_pai AS parent, ut1.codigo_extensao AS ext, ut1.codigo_usuario_inclusao AS uploader
    FROM upload_temp ut1
    WHERE ut1.codigo = 576

    UNION ALL

    SELECT ut2.codigo, ut2.codigo_upload_temp_pai AS parent, ut2.codigo_extensao AS ext, ut2.codigo_upload_temp_pai AS uploader
    FROM upload_temp ut2
    JOIN all_uploads au ON au.parent = ut2.codigo
    WHERE (SELECT ut3.codigo FROM upload_temp ut3 WHERE ut3.codigo_upload_temp_pai = ut2.codigo LIMIT 1) IS NULL
    AND ext IS NULL
    AND uploader = 1535
 )
 DELETE FROM upload_temp WHERE codigo IN (SELECT codigo FROM all_uploads);

I thought the only way to check if a folder is empty is to perform a sub-select considering the self relationship. If SELECT ut3.codigo FROM upload_temp ut3 WHERE ut3.codigo_upload_temp_pai = ut2.codigo LIMIT 1) IS NULL returns true, so the folder is empty. And by using the self referencing feature (same DB table for folders and files), I know it's a folder by checking codigo_extensao field (only files have extensions).

Well, it's not working, it removes only my 5.jpg. Any hint? Thanks in advance!

Answer 1

You can't DELETE recursively like you want. The logic here is to create a query that deletes everything you want, and run it recursively until there is anything more to delete.

Here is a function that does exactly what you need:

CREATE OR REPLACE FUNCTION p_remove_empty_folders(_codigo_usuario_ integer) RETURNS integer AS $$
DECLARE
    AFFECTEDROWS integer;
BEGIN

    WITH a AS (
    DELETE FROM upload_temp WHERE codigo IN (SELECT ut1.codigo FROM upload_temp ut1 WHERE ut1.codigo_usuario_inclusao = _codigo_usuario_ AND ut1.codigo_extensao IS NULL AND NOT EXISTS (SELECT * FROM upload_temp ut2 WHERE ut2.codigo_upload_temp_pai = ut1.codigo)) 
    RETURNING 1
    )
    SELECT count(*) INTO AFFECTEDROWS FROM a;    

    WHILE AFFECTEDROWS > 0 LOOP

        WITH a AS (
        DELETE FROM upload_temp WHERE codigo IN (SELECT ut1.codigo FROM upload_temp ut1 WHERE ut1.codigo_usuario_inclusao = _codigo_usuario_ AND ut1.codigo_extensao IS NULL AND NOT EXISTS (SELECT * FROM upload_temp ut2 WHERE ut2.codigo_upload_temp_pai = ut1.codigo)) 
        RETURNING 1
        )
        SELECT count(*) INTO AFFECTEDROWS FROM a;

    END LOOP;

    RETURN 0;
END;
$$ LANGUAGE plpgsql;

Cya!