how to update the tree path of the table in PostgreSQL



  • I have a menu table in PostgreSQL 13 like this:

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

    and the menu data look like this with a tree structure that marked by parent_id and 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);
    

    now I want to generate the full path of the parent-child id, for example, the tree_id_path for job may look like this: 5-1-13, the interview may look like 5-1-13-14. what should I do to make it work like this?



  • Here is one example, you probably need to adjust it slightly:

    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

    https://dbfiddle.uk/?rdbms=postgres_14&fiddle=627220f511fc70e6f9d455cad2782330




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2