Meet a need to transform to the hierarchy of tree structure to store department for cross table, high difficulty, want to several days didn't solve, please advice!!!!! First, thank you ~ ~ ~!!!!!!!!!!

The source table structure:
-- - | | - ID - department name - - | | - department manager - superior departments - |
| | 1 | A department zhang SAN | |
| 2 | | 1 | | A - 1 department, dick, and harry
| 2 | 3 | A - department fifty and | | 1
| | | 4 A - 1-1 department tan six | 2 |
|... |... |... |... |
Source table in the hierarchy is not fixed, the volume of data in about 7 ~ 8 departments have 300 + article

Hope output structure:
| - ID - | - department name - | - department manager - | -- - | - n n level department level department name - | -- - | - n - 1 department... -- - | | - level 1 department
| | 2 | A - 1 department li si | | | | | | A department
| | 3 | A - 2 department fifty and | | | | | | A department
| | 3 | A - 1-1 department fifty and | | | | | A - 1-1 department department A |
|... |

Ask each ace to give directions!!!!!!!!!!

CodePudding user response:

Can use ltree extension to achieve similar functionality: https://www.postgresql.org/docs/13/ltree.html

For example:
Bill=# select * from ltreetest;
C1
-- -- -- -- -- -- --
A
A. 1
A. 1.1
A. 2
A. 1.2
A. 2.1
(6 rows)

Bill=# select * from ltreetest where c1 @ & gt; 1.2 ' 'a.;
C1
-- -- -- -- -- -- --
A
A. 1
A. 1.2
(3 rows)