Postgresql树状结构递归查询

• 在平时做商品分类的过程中我们经常需要使用树状结构查询，postgresql可以通过递归查询实现该功能

  with recursive cte as
                  (
                      -- 先查询root节点
                      select id,
                              code,
                              pid,
                              ''                                   as pcode,
                              array_append(ARRAY []::text[], code) as branch
                      from tree_data
                      where id = 1
                      union all
                      -- 通过cte递归查询root节点的直接子节点
                      select origin.id,
                              origin.code,
                              cte.id   as pid,
                              cte.code as pcode,
                              array_append(cte.branch, origin.code)
                      from cte
                                  join tree_data as origin on origin.pid = cte.id
                  )
  select id,
      code,
      pid,
      pcode,
      branch,
      -- 通过计算分隔符的个数，模拟计算出树形的深度
      array_length(branch, 1)
  from cte;