Skip to content

Instantly share code, notes, and snippets.

@westc
Last active October 28, 2018 02:34
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save westc/71d2894eb0d109933a619df9d42ac3f7 to your computer and use it in GitHub Desktop.
Save westc/71d2894eb0d109933a619df9d42ac3f7 to your computer and use it in GitHub Desktop.
Recursive PostgreSQL CTE example for seeing chain of command for employees.
WITH RECURSIVE subordinates AS ( -- CTE for employees with chain of command
WITH employees AS ( -- CTE for example employees
SELECT 1 AS id, 'Davy Crockett' AS full_name, NULL AS manager_id
UNION SELECT 2 AS id, 'Cindy Cruz' AS full_name, 1 AS manager_id
UNION SELECT 3 AS id, 'Harry Thompson' AS full_name, 1 AS manager_id
UNION SELECT 4 AS id, 'George Jefferson' AS full_name, 2 AS manager_id
UNION SELECT 5 AS id, 'Blinky Bill' AS full_name, 2 AS manager_id
UNION SELECT 6 AS id, 'Linda Vernsky' AS full_name, 1 AS manager_id
UNION SELECT 7 AS id, 'Abigail Quispe' AS full_name, 1 AS manager_id
UNION SELECT 8 AS id, 'Jessica Ericson' AS full_name, 7 AS manager_id
UNION SELECT 9 AS id, 'Matthew Haywood' AS full_name, 7 AS manager_id
UNION SELECT 10 AS id, 'Angel Jones' AS full_name, 7 AS manager_id
UNION SELECT 11 AS id, 'Chris Mackenzie' AS full_name, 7 AS manager_id
UNION SELECT 12 AS id, 'Gustavo Frederickson' AS full_name, 5 AS manager_id
UNION SELECT 13 AS id, 'Joan Smith' AS full_name, 5 AS manager_id
UNION SELECT 14 AS id, 'Debora Ferreira' AS full_name, 5 AS manager_id
)
SELECT -- non-recursive term
id,
manager_id,
full_name,
ARRAY[full_name] AS chain_of_command_names
FROM employees
WHERE manager_id IS NULL
UNION
SELECT -- recursive term
e.id,
e.manager_id,
e.full_name,
array_append(s.chain_of_command_names, e.full_name) AS chain_of_command_names
FROM employees e
JOIN subordinates s
ON s.id = e.manager_id
)
SELECT id, full_name, chain_of_command_names
FROM subordinates
ORDER BY id;
@westc
Copy link
Author

westc commented Oct 27, 2018

@westc
Copy link
Author

westc commented Oct 28, 2018

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment