Postgresql - update a tree table with rows from one table by changing id and parent_id

Question

I have 2 tables:

\d folder

  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 files

  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) 

Now I need a function or cursor or any thing that gets two inputs, a copy folder and a destination folder for copying, the function should copy the folder and its child folders to the same table with the new id and parent identifier, as shown below, then the same time when the folder is copied and pasted, the file in the file table should also be pasted, plz help me get the result below.

If I copy folder5 to folder3, my output should look 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 the file table should also be updated when copying and pasting the folder as follows:

  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) 

Answer 1

 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; 

it will do as i thought ...

Answer 2

why not use a simple update?

 BEGIN; UPDATE folder SET parent_id = 3 WHERE id = 5; UPDATE files SET folder_id = 3 WHERE folder_id = 5; END;