PostgreSQL: Common Table Expressions or CTEs:

A common table expression is a temporary result set which you can reference within another SQL statement including INSERT, SELECT, UPDATE & DELETE.

Common Table Expressions are temporary in the sense that they only exist during the execution of the query.

The following shows the syntax of creating a CTE:

WITH cte_name (column_list) AS (
    CTE_query_definition 
)
statement;

In this syntax:

• First, specify the name of the CTE following by an optional column list.
• Second, inside the body of the WITH clause, specify a query that returns a result set. If you do not explicitly specify the column list after the CTE name, the select list of the CTE_query_definitionwill become the column list of the CTE.
• Third, use the CTE like a table or view in the statement which can be a SELECT, INSERT, UPDATE, or DELETE.

PostgreSQL CTE examples:

 The recursive queries are useful in many situations such as for querying hierarchical data like organizational structure, bill of materials, etc.

The following illustrates the detailled syntax of a recursive CTE:

WITH RECURSIVE cte_name(
    CTE_query_definition -- non-recursive term
    UNION [ALL]
    CTE_query definion  -- recursive term
) SELECT * FROM cte_name;

A recursive CTE has three elements:

• Non-recursive term: the non-recursive term is a CTE query definition that forms the base result set of the CTE structure.
• Recursive term: the recursive term is one or more CTE query definitions joined with the non-recursive term using the UNION or UNION ALL operator. The recursive term references to the CTE name itself.
• Termination check: the recursion stops when no rows are returned from the previous iteration.

PostgreSQL executes a recursive CTE in the following sequence:

1. Execute the non-recursive term to create the base result set (R0).
2. Execute recursive term with Ri as an input to return the result set Ri+1 as the output.
3. Repeat step 2 until an empty set is returned. (termination check)
4. Return the final result set that is a UNION or UNION ALL of the result set R0, R1, … Rn

CREATE TABLE Employees (
EmpId INT NOT NULL PRIMARY KEY,
EmpName VARCHAR(50) DEFAULT NULL,
ManagerId INT DEFAULT NULL,
ManagerName VARCHAR(50) DEFAULT NULL
)


INSERT INTO employees (EmpId,EmpName,ManagerId, ManagerName) VALUES (1,'Sai','2','Aarti');
INSERT INTO employees (EmpId,EmpName,ManagerId, ManagerName) VALUES (2,'Aarti','3', 'Mike');
INSERT INTO employees (EmpId,EmpName,ManagerId, ManagerName) VALUES (3,'Mike','4', 'John');
INSERT INTO employees (EmpId,EmpName,ManagerId, ManagerName) VALUES (4,'John','5', 'Jayasree');
INSERT INTO employees (EmpId,EmpName,ManagerId, ManagerName) VALUES (5,'Jayasree',NULL, NULL);
INSERT INTO employees (EmpId,EmpName,ManagerId, ManagerName) VALUES (6,'Philip','5', 'Jayasree');
INSERT INTO employees (EmpId,EmpName,ManagerId, ManagerName) VALUES (7,'Christain','4', 'John');

The following query returns all managers of the employee with the id 1.

WITH RECURSIVE managers as 
(
    select 
    	0 as level,
    	empid,
    	empname,
    	managerid,
    	managername
    from employees
    where empid = 1
UNION
    SELECT
    	level + 1,
    	e.empid,
    	e.empname,
    	e.managerid,
    	e.managername
    from employees e
    join managers m on m.managerid = e.empid
)
select 
	*, 
	case when level = 0 then 'SELF' else 'MANAGER' end as relation
from managers order by level

The above output implies that SAI manager is Aarti, Aarti manager is Mike, Mike manager is John, John manager is Jayasree and Jayasree have no managers on top of her, so the query terminated.

The following query returns all reportees of the employee with the id 5.

WITH RECURSIVE reportees as 
(
    select 
    	0 as level,
    	empid,
    	empname,
    	managerid,
    	managername
    from employees
    where empid = 5
UNION
    SELECT
    	level + 1,
    	e.empid,
    	e.empname,
    	e.managerid,
    	e.managername
    from employees e
    join reportees r on r.empid = e.managerid
)
select 
	*, 
	case when level = 0 then 'SELF' else 'REPORTER' end as relation
from reportees order by level