PostgreSQL Recursive Queries

2.1 What is a Recursive Query?

A recursive query is a type of Common Table Expression (CTE) that references itself to generate a result set. It is particularly useful for traversing hierarchical data.

2.2 Recursive Common Table Expressions (CTE)

Recursive CTEs are defined using the WITH RECURSIVE clause. They consist of two parts: the base case and the recursive case.

The base case provides the initial result set, while the recursive case references the CTE to build upon the results iteratively.

3.1 Basic Syntax

WITH RECURSIVE cte_name AS (
    -- Base case
    SELECT columns FROM table WHERE condition
    UNION ALL
    -- Recursive case
    SELECT columns FROM cte_name JOIN table ON condition
)
SELECT * FROM cte_name;

4.1 Example: Employee Hierarchy

Suppose we have a table employees with the following structure:

CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    manager_id INT REFERENCES employees(id)
);

To retrieve the hierarchy of employees under a specific manager:

WITH RECURSIVE employee_hierarchy AS (
    SELECT id, name, manager_id FROM employees WHERE manager_id IS NULL
    UNION ALL
    SELECT e.id, e.name, e.manager_id FROM employees e
    JOIN employee_hierarchy eh ON e.manager_id = eh.id
)
SELECT * FROM employee_hierarchy;