0Lta3kuP

1CREATE OR REPLACE FUNCTION hierarchy() RETURNS void LANGUAGE plpgsql AS $$
2
3DECLARE
4
5LBower INT;
6
7BEGIN 
8--===========================================================================
9--      1.  Read ALL the nodes in a given level as indicated by the parent/
10--          child relationship in the Adjacency List.
11--      2.  As we read the nodes in a given level, mark each node with the 
12--          current level number.
13--      3.  As we read the nodes in a given level, convert the EmployeeID to
14--          a Binary(4) and concatenate it with the parents in the previous
15--          level's binary string of EmployeeID's.  This will build the 
16--          SortPath.
17--      4.  Number the rows according to the Sort Path.  This will number the
18--          rows in the same order that the push-stack method would number 
19--          them.
20--===========================================================================
21
22--===== Conditionally drop Temp tables to make reruns easy
23    DROP TABLE IF EXISTS Hierarchy;
24	
25	CREATE TABLE Hierarchy 
26        (
27        EmployeeID INT NOT NULL,
28        ManagerID INT,
29        HLevel  INT,
30        LeftBower INT,
31        RightBower INT,
32        NodeNumber INT,
33        NodeCount INT,
34		SortPath BIT VARYING(16000)
35        )
36;
37
38--===== Build the new table on-the-fly including some place holders
39   WITH RECURSIVE cteBuildPath AS 
40( --=== This is the "anchor" part of the recursive CTE.
41     -- The only thing it does is load the Root Node.
42 SELECT anchor.EmployeeID, 
43        anchor.ManagerID, 
44        1 AS HLevel,
45--		anchor.EmployeeID::bit(16000) AS SortPath --Up to 1000 levels deep.
46		cast(CAST(anchor.EmployeeID as BIT(16)) as bit varying) AS SortPath --Up to 1000 levels deep.
47   FROM Employee AS anchor
48  WHERE ManagerID IS NULL --Only the Root Node has a NULL ManagerID
49  UNION ALL 
50 --==== This is the "recursive" part of the CTE that adds 1 for each level
51     -- and concatenates each level of EmployeeID's to the SortPath column.  
52 SELECT recur.EmployeeID, 
53        recur.ManagerID, 
54        cte.HLevel + 1 AS HLevel,
55        CAST( --This does the concatenation to build SortPath
56                        cte.SortPath || CAST(Recur.EmployeeID AS BIT(16))
57                    AS BIT VARYING) as SortPath
58   FROM Employee      AS recur
59  INNER JOIN cteBuildPath AS cte 
60          ON cte.EmployeeID = recur.ManagerID
61) --=== This final SELECT/INTO creates the Node # in the same order as a
62     -- push-stack would. It also creates the final table with some
63     -- "reserved" columns on the fly. We'll leave the SortPath column in
64     -- place because we're still going to need it later.
65     -- The ISNULLs make NOT NULL columns.
66	INSERT INTO Hierarchy
67 SELECT coalesce(sorted.EmployeeID,0) as EmployeeID,
68        sorted.ManagerID,
69        coalesce(sorted.HLevel,0) as HLevel,
70        coalesce(CAST(0 AS INT),0) as LeftBower, --Place holder
71        coalesce(CAST(0 AS INT),0) as RightBower, --Place holder
72        ROW_NUMBER() OVER (ORDER BY sorted.SortPath) as NodeNumber,
73        coalesce(CAST(0 AS INT),0) as NodeCount, --Place holder
74        coalesce(sorted.SortPath,sorted.SortPath) as SortPath
75   FROM cteBuildPath AS sorted
76 --OPTION (MAXRECURSION 100) --Change this IF necessary
77;
78--===========================================================================
79--      5.  Once the data from Steps 1, 2, 3, AND 4 is complete, update that
80--          data with the calculated Left Bower.
81--===========================================================================
82--===== Calculate the Left Bower
83 UPDATE Hierarchy
84    SET LeftBower = 2 * NodeNumber - HLevel
85;
86
87--===== Create the Nested Sets from the information available in the table
88     -- and in the following CTE. This uses the proprietary form of UPDATE
89     -- available in SQL Serrver for extra performance.
90
91drop table if exists HTally;
92	 
93create table HTally (N int);
94
95insert into HTally
96select (row_number() over (ORDER BY (SELECT 0)) - 1) * 16 as N
97from employee e
98limit 1000;
99
100WITH cteCountDownlines AS
101( --=== Count each occurance of EmployeeID in the sort path
102 SELECT CAST(SUBSTRING(h.SortPath,t.N + 1,16) AS INT) as EmployeeID, 
103        COUNT(*) as NodeCount --Includes current node
104   FROM Hierarchy h, 
105        HTally t
106  WHERE t.N BETWEEN 0 AND octet_length(SortPath) * 8 AND EmployeeID > 0
107  GROUP BY CAST(SUBSTRING(h.SortPath,t.N + 1,16) AS INT)
108) --=== Update the NodeCount and calculate both Bowers
109 UPDATE hierarchy
110    SET 
111		LeftBower = 2 * h.NodeNumber - h.HLevel,
112        NodeCount  = downline.NodeCount,
113        RightBower = (downline.NodeCount - 1) * 2 + hierarchy.LeftBower + 1
114   FROM Hierarchy h
115   JOIN cteCountDownlines downline
116     ON h.EmployeeID = downline.EmployeeID
117	WHERE hierarchy.EmployeeID = downline.EmployeeID
118;
119
120END
121$$;
122SELECT hierarchy();