Custom sort order on hierarchical query

How to make Project Manager 2 sort before Project Manager 1. Tester 4 sort before Tester 3?

emp_id |            emp_name             
--------+---------------------------------
      1 | President
      2 |   Vice President
      3 |     CEO
      4 |     CTO
      5 |       Group Project Manager
      6 |         Project Manager 1
      8 |           Team Leader 1
      9 |             Software Engineer 1
     10 |             Software Engineer 2
     11 |           Test Lead 1
     12 |             Tester 1
     13 |             Tester 2
      7 |         Project Manager 2
     14 |           Team Leader 2
     15 |             Software Engineer 3
     16 |             Software Engineer 4
     17 |           Test Lead 2
     18 |             Tester 3
     19 |             Tester 4
     20 |             Tester 5
(20 rows)

To produce the data, expand the code and copy it

select *
into emp
from 
(values
(1, 'President', NULL),
(2, 'Vice President', 1),
(3, 'CEO', 2),
(4, 'CTO', 2),
(5, 'Group Project Manager', 4),
(6, 'Project Manager 1', 5),
(7, 'Project Manager 2', 5),
(8, 'Team Leader 1', 6),
(9, 'Software Engineer 1', 8),
(10, 'Software Engineer 2', 8),
(11, 'Test Lead 1', 6),
(12, 'Tester 1', 11),
(13, 'Tester 2', 11),
(14, 'Team Leader 2', 7),
(15, 'Software Engineer 3', 14),
(16, 'Software Engineer 4', 14),
(17, 'Test Lead 2', 7),
(18, 'Tester 3', 17),
(19, 'Tester 4', 17),
(20, 'Tester 5', 17)
) as x(emp_id, emp_name, mgr_id)

Normal hierachical query with no overriding of sorting:

with recursive org(emp_id, emp_name, emp_level, mgr_id, sort) as
(
select  
a.emp_id, a.emp_name, 0, a.mgr_id,  
a.emp_name
from emp a
where a.mgr_id is null

union all

select 
b.emp_id, b.emp_name, emp_level + 1, b.mgr_id, 

sort || ' : ' || b.emp_name 

from emp b
join org on org.emp_id = b.mgr_id
)
select 
emp_id, repeat(' ', emp_level * 2) || emp_name as emp_name
from org
order by sort

As we can see, the main logic of sorting, comes from text concatenation:

sort || ' : ' || b.emp_name 

Now how do we override the sorting from that text? Simple, just change the b.name to number when there's an overriding(indicated by non-null value) of sort, but since the sorting is textual, we must also convert the number to text.

Here's the amended code for overriding sorting:

sort || ' : ' || coalesce( lpad(order_override::text, 10, '0'), b.emp_name )

Please do note that we must pad zeroes on number, otherwise 12 will sort after 1, for example: 1, 12, 2, 3

Now let's add the sorting override field:

alter table emp add column order_override int null;

update emp set order_override = 1 where emp_id = 7; -- PM 2
update emp set order_override = 2 where emp_id = 6; -- PM 1

update emp set order_override = 1 where emp_id = 19; -- Tester 4
update emp set order_override = 2 where emp_id = 18; -- Tester 3

Final query:

with recursive org(emp_id, emp_name, emp_level, mgr_id, sort) as
(
 select  
 a.emp_id, a.emp_name, 0, a.mgr_id,  
 a.emp_name
 from emp a
 where a.mgr_id is null

 union all

 select 
 b.emp_id, b.emp_name, emp_level + 1, b.mgr_id, 

 sort || ' : ' || coalesce( lpad(order_override::text, 10, '0'), b.emp_name )
 from emp b
 join org on org.emp_id = b.mgr_id
)
select 
emp_id, repeat(' ', emp_level * 2) || emp_name as emp_name
from org
order by sort

emp_id |            emp_name             
--------+---------------------------------
      1 | President
      2 |   Vice President
      3 |     CEO
      4 |     CTO
      5 |       Group Project Manager
      7 |         Project Manager 2
     14 |           Team Leader 2
     15 |             Software Engineer 3
     16 |             Software Engineer 4
     17 |           Test Lead 2
     19 |             Tester 4
     18 |             Tester 3
     20 |             Tester 5
      6 |         Project Manager 1
      8 |           Team Leader 1
      9 |             Software Engineer 1
     10 |             Software Engineer 2
     11 |           Test Lead 1
     12 |             Tester 1
     13 |             Tester 2
(20 rows)

As we can see above, Project Manager 2 now comes before Project Manager 1; Tester 4 now comes before Tester 3.