当前位置: 首页 > 面试题库 >

PostgreSQL-通过更改id和parent_id用来自同一表的行更新树表

黎震博
2023-03-14
问题内容

我有2张table:

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

现在我需要一个函数或光标或任何会得到两个输入的东西,即要复制的文件夹和要复制的目标文件夹,该函数应将文件夹及其子文件夹复制到具有新ID和父ID的同一表中,如下所示当复制并插入文件夹时,文件表中的文件也将被插入,请帮助我获得以下结果。

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

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)

当复制和插入文件夹时,文件表也将被更新,如下所示:

 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;

这会按照我的想法做…



 类似资料:
  • 问题内容: 我是Postgresql的新手,正在使用9.3版。我有一张桌子,里面有几行。我的问题是,当我更新一行时,该行号被更改并将其移动到表中的最后一个位置。我的问题是:这是否是默认行为,因为我认为在更新行时,不应将其从其位置移开?该操作似乎就像先删除然后再插入该行。 这是示例SQL: 从今起: 将ID为1的更新后的行移动到最后一个位置。 谢谢 问题答案: 行号已更改 关系表中没有“行号”之类的

  • 问题内容: 我正在尝试使用同一表中不同行(和不同列)中的值更新表中的行。尽管我的语法没有任何结果,但与此类似:这是代码(已更新): 问题答案: 或者,简单地

  • 我希望在一条语句中更新PostgreSQL中的多行。有没有一种方法可以做到以下几点?

  • 问题内容: 我正在尝试使用相同的表查询更新行。语境: 我想:对于每一行;如果 TEXT为NULL ; 使用具有相同ID且LANG =’EN’的行的TEXT值更新它。 做这样的事情的SQL请求是什么? 问题答案: 您没有指定数据库。以下是标准SQL: 如果出现重复,则应执行以下操作: 编辑: 当然,并非所有数据库都支持所有ANSI标准功能。在MySQL中,您可以改用:

  • 问题内容: 是否可以在带有子选择的mysql 5.0上运行UPDATE命令。 我要运行的命令是这样的: ISBN13当前存储为字符串。 这应该更新10k +行。 谢谢, 威廉 问题答案: 只需更改一下即可:

  • 问题内容: 我想更新一个表以指示某些行是其他行的父表,因此我在表中添加了“父代”列。以下查询查找所有父母: 但是当我尝试修改该语法以进行更新时,它不起作用: 我得到: 请注意,第7行第28列是“ SET”行的结尾。 问题答案: Oracle不支持语句中的子句。 用这个: