删除递归PostgreSQL [英] DELETE recursive PostgreSQL

问题描述

我有一个表 upload_temp ，如下所示：

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

此表存储了我系统的所有临时上载，这些上载可以是文件夹和文件。表格上的自我参考功能可以解决这个问题。但问题是：将某些文件注册到系统后，该文件成为正式的数字文件，并且删除了 upload_temp 。删除后，仅当其父文件夹变为空文件夹时，才必须删除其父文件夹。通过这种方式，只要它们失去了唯一的子文件夹而将其清空，就需要从该树中删除所有文件夹。下图包含更多详细信息：

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:

文件夹和文件树

文件 5.jpg 属于文件夹5 ，该文件属于到 4号文件夹，依此类推。如果我选择文件 5.jpg 要注册到系统，则会从 upload_temp 中删除该文件，这将清空 5号文件夹。因此，文件夹5 （如果为空，也必须删除），在这种情况下，所有父文件夹也会发生同样的情况。

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.

尽管我使用的是PHP，但我仍然需要PostgreSQL中针对性能的解决方案。我正在尝试了解具有递归的工作原理，但是遇到了困难。我编写了以下代码，该代码应该从文件 5.jpg 开始以递归方式删除所有父母，而无视空白功能：

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

下图显示了 SELECT * FROM upload_temp ORDER BY codigo的结果：

选择结果

嗯，它不起作用。它仅删除一个文件。我该怎么解决？谢谢！

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

推荐答案

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

您必须将起始点放在初始选择中（在with内），或者您必须以某种方式制作一个代表树顶部的伪列，这对整个树中的每一行都是相同的。将顶部位置放在with内的初始选择中是更简单的解决方案。

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.

这篇关于删除递归PostgreSQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！