Início / dba / Perguntas / 311254
Accepted
Dolphin
Asked: 2022-04-24 00:29:35 +0800 CST

como atualizar o caminho da árvore da tabela no PostgreSQL

  • 772

Eu tenho uma tabela de menus no PostgreSQL 13 assim:

-- Drop table

-- DROP TABLE public.menu_resource;

CREATE TABLE public.menu_resource (
    id int4 NOT NULL GENERATED ALWAYS AS IDENTITY,
    "name" varchar NOT NULL,
    res_type int4 NOT NULL,
    created_time int8 NOT NULL,
    updated_time int8 NOT NULL,
    remark varchar NULL,
    "path" varchar NOT NULL,
    parent_id int4 NOT NULL,
    component varchar NULL,
    sort int4 NOT NULL,
    name_zh varchar NOT NULL,
    tree_id_path varchar NULL,
    CONSTRAINT auth_resource_id_seq PRIMARY KEY (id)
);

e os dados do menu se parecem com isso com uma estrutura de árvore marcada por parent_ide id:

INSERT INTO public.menu_resource
(id, "name", res_type, created_time, updated_time, remark, "path", parent_id, component, sort, name_zh, tree_id_path)
VALUES(13, 'job', 1, 1632389739938, 1632389739938, NULL, '/app/job', 1, NULL, 2, '求职管理', '5-1-13');
INSERT INTO public.menu_resource
(id, "name", res_type, created_time, updated_time, remark, "path", parent_id, component, sort, name_zh, tree_id_path)
VALUES(14, 'interview', 1, 1632389739938, 1632389739938, NULL, '/app/job/interview', 13, NULL, 2, '面试列表', '5-1-13-14');
INSERT INTO public.menu_resource
(id, "name", res_type, created_time, updated_time, remark, "path", parent_id, component, sort, name_zh, tree_id_path)
VALUES(15, 'menu', 1, 1632389739938, 1632389739938, NULL, '/privilege/menu', 2, './permission/menu', 0, '菜单列表', NULL);
INSERT INTO public.menu_resource
(id, "name", res_type, created_time, updated_time, remark, "path", parent_id, component, sort, name_zh, tree_id_path)
VALUES(3, 'cruise', 1, 1632389739938, 1632389739938, NULL, '/app/cruise', 1, NULL, 2, 'Cruise', NULL);
INSERT INTO public.menu_resource
(id, "name", res_type, created_time, updated_time, remark, "path", parent_id, component, sort, name_zh, tree_id_path)
VALUES(5, '系统菜单', 1, 1632389739938, 1632389739938, NULL, '/demo', 0, NULL, 0, '系统菜单', NULL);
INSERT INTO public.menu_resource
(id, "name", res_type, created_time, updated_time, remark, "path", parent_id, component, sort, name_zh, tree_id_path)
VALUES(4, 'channel', 1, 1632389739938, 1632389739938, NULL, '/app/cruise/channel', 3, './apps/cruise/channel', 3, '频道', NULL);
INSERT INTO public.menu_resource
(id, "name", res_type, created_time, updated_time, remark, "path", parent_id, component, sort, name_zh, tree_id_path)
VALUES(1, 'app', 1, 1632389739938, 1632389739938, NULL, '/app', 5, NULL, 1, '应用', NULL);
INSERT INTO public.menu_resource
(id, "name", res_type, created_time, updated_time, remark, "path", parent_id, component, sort, name_zh, tree_id_path)
VALUES(2, 'privilege', 1, 1632389739938, 1632389739938, NULL, '/privilege', 5, NULL, 4, '权限管理', NULL);
INSERT INTO public.menu_resource
(id, "name", res_type, created_time, updated_time, remark, "path", parent_id, component, sort, name_zh, tree_id_path)
VALUES(6, 'role', 1, 1632389739938, 1632389739938, NULL, '/privilege/role', 2, './permission/role', 5, '角色列表', NULL);
INSERT INTO public.menu_resource
(id, "name", res_type, created_time, updated_time, remark, "path", parent_id, component, sort, name_zh, tree_id_path)
VALUES(7, 'article', 1, 1632389739938, 1632389739938, NULL, '/app/cruise/article', 3, './apps/cruise/article', 3, '文章', NULL);
INSERT INTO public.menu_resource
(id, "name", res_type, created_time, updated_time, remark, "path", parent_id, component, sort, name_zh, tree_id_path)
VALUES(8, 'user', 1, 1632389739938, 1632389739938, NULL, '/privilege/user', 2, './permission/user', 5, '用户列表', NULL);
INSERT INTO public.menu_resource
(id, "name", res_type, created_time, updated_time, remark, "path", parent_id, component, sort, name_zh, tree_id_path)
VALUES(9, 'welcome', 1, 1632389739938, 1632389739938, NULL, '/welcome', 5, './Welcome', 0, '欢迎', NULL);
INSERT INTO public.menu_resource
(id, "name", res_type, created_time, updated_time, remark, "path", parent_id, component, sort, name_zh, tree_id_path)
VALUES(10, 'overview', 1, 1632389739938, 1632389739938, NULL, '/app/overview', 1, NULL, 2, '应用概览', NULL);
INSERT INTO public.menu_resource
(id, "name", res_type, created_time, updated_time, remark, "path", parent_id, component, sort, name_zh, tree_id_path)
VALUES(11, 'product', 1, 1632389739938, 1632389739938, NULL, '/app/overview/product', 10, NULL, 2, '产品列表', NULL);
INSERT INTO public.menu_resource
(id, "name", res_type, created_time, updated_time, remark, "path", parent_id, component, sort, name_zh, tree_id_path)
VALUES(12, 'list', 1, 1632389739938, 1632389739938, NULL, '/app/overview/list', 10, NULL, 2, '应用列表', NULL);

agora eu quero gerar o caminho completo do id pai-filho, por exemplo, o tree_id_pathfor job pode ficar assim: 5-1-13, a entrevista pode parecer 5-1-13-14. o que devo fazer para que funcione assim?

postgresql tree

1 respostas

  1. Best Answer

    Aqui está um exemplo, você provavelmente precisará ajustá-lo um pouco:

    create table tmp (id int, parent_id int, tree_path_id text);

insert into tmp (id, parent_id, tree_path_id)
with recursive t (id, parent_id, tree_path_id) as (
    select id, parent_id, '' as tree_path_id 
    from public.menu_resource 
    where parent_id = 0
    
    union all
    
    select mr.id, mr.parent_id, t.tree_path_id || '-' || mr.parent_id::text
    from public.menu_resource mr
    join t
        on mr.parent_id = t.id
) 
select id, parent_id, tree_path_id from t;

update public.menu_resource x
    set tree_id_path = (select tree_path_id from tmp where id = x.id);

select id, parent_id, tree_id_path from public.menu_resource;

id  parent_id   tree_id_path
13          1   -5-1
14         13   -5-1-13
15          2   -5-2
3           1   -5-1
5           0   
4           3   -5-1-3
1           5   -5
2           5   -5
6           2   -5-2
7           3   -5-1-3
8           2   -5-2
9           5   -5
10          1   -5-1
11         10   -5-1-10
12         10   -5-1-10

    violino

    • 1

relate perguntas