PostgreSQL-通过更改id和parent_id用来自同一表的行更新树表 [英] postgresql - update tree table with rows from same table by changing id andparent_id

问题描述

我有2张桌子：

\d文件夹

                              Table "public.folder"
Column   |         Type          |                      Modifiers                      
---------+-----------------------+----------------------------------------------------
id        | integer               | not null default nextval('folder_id_seq'::regclass)
name      | character varying(20) | 
parent_id | integer               | 

Indexes:
  "folder_pkey" PRIMARY KEY, btree (id)

Foreign-key constraints:
  "fk_folder_1" FOREIGN KEY (parent_id) REFERENCES folder(id)

Referenced by:
  TABLE "files" CONSTRAINT "fk_files_1" FOREIGN KEY (folder_id) REFERENCES folder(id)
  TABLE "folder" CONSTRAINT "fk_folder_1" FOREIGN KEY (parent_id) REFERENCES folder(id)

\d个文件

                              Table "public.files"
Column    |         Type          |                     Modifiers                      
----------+-----------------------+---------------------------------------------------
id        | integer               | not null default nextval('files_id_seq'::regclass)
name      | character varying(20) | 
folder_id | integer               | 

Indexes:
  "files_pkey" PRIMARY KEY, btree (id)

Foreign-key constraints:
  "fk_files_1" FOREIGN KEY (folder_id) REFERENCES folder(id)


select * from folder;
 id |  name   | parent_id 
----+---------+-----------
  1 | home    |          
  2 | folder2 |         1
  3 | folder3 |         1
  4 | folder4 |         2
  5 | folder5 |         4
  6 | folder6 |         5

(6 rows)



select * from files;
 id | name  | folder_id 
----+-------+-----------
  1 | file1 |         4
  2 | file2 |         4
  3 | file3 |         5
  4 | file4 |         6
  5 | file5 |         6
  6 | file6 |         2
(6 rows)

现在我需要一个函数或游标或任何会获得两个输入，即要复制的文件夹和要复制的目标文件夹，此功能应将文件夹及其子文件夹复制到具有新ID和父ID的同一表中，如下所示，同时复制文件夹并将文件插入文件表中的时间为还可以插入，请帮助我得到以下结果。

Now I need a function or cursor or any thing which will get two inputs,folder to copy and destination folder to copy, the function should copy the folder and its child folders into the same table with new id and parent id as below, same time when folder is copied and inserted the file in files table is also to be inserted, plz help me get the below result..

如果我将folder5对应到folder3，我的输出应该是这样的：

if I am coping folder5 to folder3 my output should be like this:

select * from folder;
 id |  name   | parent_id 
----+---------+-----------
  1 | home    |          
  2 | folder2 |         1
  3 | folder3 |         1
  4 | folder4 |         2
  5 | folder5 |         4
  6 | folder6 |         5
  7 | folder5 |         3
  8 | folder6 |         7
(8 rows)

复制文件夹时文件表也将更新并这样插入：

and files table is also to be updated when folder is copied and inserted like this:

 select * from files;
 id | name  | folder_id 
----+-------+-----------
  1 | file1 |         4
  2 | file2 |         4
  3 | file3 |         5
  4 | file4 |         6
  5 | file5 |         6
  6 | file6 |         2
  7 | file3 |         7
  8 | file4 |         8
  9 | file5 |         8
(9 rows)

推荐答案

CREATE OR REPLACE FUNCTION tree_copy(INTEGER,INTEGER) RETURNS VOID AS $$
DECLARE
    a ALIAS FOR $1; --ROOT FOLDER TO BE COPIED
    b ALIAS FOR $2; --DESTINATION FOLDER
    i INTEGER;
    j INTEGER;
    g INTEGER;
BEGIN
--DROP TABLE IF EXISTS temp1;

CREATE TEMPORARY TABLE temp1 AS(
WITH RECURSIVE CTE AS(
SELECT *, NEXTVAL('folder_id_seq') new_id FROM folder WHERE id = a
UNION ALL
SELECT folder.*,NEXTVAL('folder_id_seq') new_id FROM CTE
JOIN folder ON CTE.id = folder.parent_id)
SELECT C1.id, C1.new_id, C1.parent_id, 
C2.new_id new_parent_id FROM CTE C1 LEFT JOIN
CTE C2 ON C1.parent_id = C2.id);

FOR i IN (WITH RECURSIVE  t AS(SELECT id, parent_id FROM folder WHERE id = a
UNION SELECT f.id,f.parent_id FROM folder f, t AS t1 WHERE f.parent_id = t1.id)
SELECT id FROM t)
LOOP
    SELECT new_parent_id INTO g FROM temp1 WHERE id = i;

    INSERT INTO folder(id,name,parent_id)VALUES(
    (SELECT new_id FROM temp1 WHERE id = i),
    (SELECT name FROM folder WHERE id = i),COALESCE(g,b));

    FOR j IN (SELECT id FROM files WHERE folder_id = i)
    LOOP
        INSERT INTO files(id,name,folder_id) VALUES (
        NEXTVAL('files_id_seq'),(SELECT name FROM files WHERE id = j),
        (SELECT new_id FROM temp1 WHERE id = i));
    END LOOP;
END LOOP;
DROP TABLE temp1;
END;
$$ LANGUAGE PLPGSQL; 

这将按照我的想法做...

this will do as i thought...

这篇关于PostgreSQL-通过更改id和parent_id用来自同一表的行更新树表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！