我有一个带有记录的表，可以引用同一表中的另一行，因此同一表中的行之间存在父子关系。
我试图实现的是为另一个用户创建相同的数据，以便他们可以通过 Web UI 查看和管理自己的此结构版本，其中这些行显示为树。
问题是当我仅通过更改user_id来批量插入此数据时，我丢失了行之间的关系，因为parent_id值对于这些新记录无效，并且它们也应该使用新生成的 id 进行更新。
这是我尝试过的：(没有工作(
- 迭代main_table
- 复制粘贴每个之后的静态值
- 在临时表上执行另一个插入以保存旧ID和新ID
- 循环结束后使用新 ID 更新旧parent_ids
我尝试做这样的事情(最后一步不包括在这里(
create or replace function test_x()
returns void as
$BODY$
declare
r RECORD;
userId int8;
rowPK int8;
begin
userId := (select 1)
create table if not exists id_map (old_id int8, new_id int8);
create table if not exists temp_table as select * from main_table;
for r in select * from temp_table
loop
rowPK := insert into main_table(id, user_id, code, description, parent_id)
values(nextval('hibernate_sequence'), userId, r.code, r.description, r.parent_id) returning id;
insert into id_map (old_id, new_id) values (r.id, rowPK);
end loop;
end
$BODY$
language plpgsql;
我的PostgreSQL版本是9.6.14。
下面的 DDL 进行测试。
create table main_table(
id bigserial not null,
user_id int8 not null,
code varchar(3) not null,
description varchar(100) not null,
parent_id int8 null,
constraint mycompkey unique (user_id, code, parent_id),
constraint mypk primary key (id),
constraint myfk foreign key (parent_id) references main_table(id)
);
insert into main_table (id, user_id, code, description, parent_id)
values(0, 0, '01', 'Root row', null);
insert into main_table (id, user_id, code, description, parent_id)
values(1, 0, '001', 'Child row 1', 0);
insert into main_table (id, user_id, code, description, parent_id)
values(2, 0, '002', 'Child row 2', 0);
insert into main_table (id, user_id, code, description, parent_id)
values(3, 0, '002', 'Grand child row 1', 2);
如何编写一个过程来完成此操作？
提前谢谢。
您的任务似乎是将给定用户的所有数据复制到另一个用户，同时保持新行中的层次结构关系。下面完成此操作。
它开始使用新user_id(包括旧行parent_id(创建现有行的新副本。这将是下一个(更新(步骤中的用户。
CTE 在逻辑上从具有parent_id的新行开始，并连接到旧的父行。从这里，它使用代码和说明将旧的父行连接到新的父行。此时，我们有新的id和新的父id。此时，只需使用这些值进行更新。实际上，对于更新，CTE 只需要选择这两列，但我保留了中间列，因此您可以根据需要进行跟踪。
create or replace function copy_user_data_to_user(
source_user_id bigint
, target_user_id bigint
)
returns void
language plpgsql
as $$
begin
insert into main_table ( user_id,code, description, parent_id )
select target_user_id, code, description, parent_id
from main_table
where user_id = source_user_id ;
with n_list as
(select mt.id, mt.code, mt.description, mt.parent_id
, mtp.id p_id,mtp.code p_code,mtp.description p_des
, mtc.id c_id, mtc.code c_code, mtc.description c_description
from main_table mt
join main_table mtp on mtp.id = mt.parent_id
join main_table mtc on ( mtc.user_id = target_user_id
and mtc.code = mtp.code
and mtc.description = mtp.description
)
where mt.parent_id is not null
and mt.user_id = target_user_id
)
update main_table mt
set parent_id = n_list.c_id
from n_list
where mt.id = n_list.id;
return;
end ;
$$;
-- test
select * from copy_user_data_to_user(0,1);
select * from main_table;
创建表
"您要创建的表名称" 从我的集合中选择 *
但是新表和MySet列名应该相等，您也可以 使用 * 代替列名，但新表中存在列名 其他错误