PostgreSQL – Create Recursive Views

A view in PostgreSQL can be defined as a virtual table that is derived from underlying base tables or previously defined views. A view is nothing but a SQL statement that is stored in the database with an associated name. We have looked at what is the recursive query. To store the recursive query or to make a view out of a recursive query, PostgreSQL provides recursive views. So, recursive views are nothing but stored recursive queries.

In this article, we will learn about recursive views in PostgreSQL. We will introduce you to the concept of recursive views, show how recursive views are created, And most importantly we will also see how recursive views can be implemented in PostgreSQL.

Why Use Recursive Views?

Recursive views simplify complex recursive queries by allowing you to store them as a reusable virtual table. This makes your code cleaner and easier to maintain while also improving query performance by reducing repetition.

Creating PostgreSQL Recursive Views

PostgreSQL provides two types of syntax for creating a recursive view.

Syntax:

CREATE RECURSIVE VIEW view_name(columns) 
AS
SELECT ...;                 -- SQL query

Which is equivalent to:

CREATE VIEW view_name AS
WITH RECURSIVE view_name (column_names) AS
-- recursive SQL query
SELECT ...
)
SELECT column_names
FROM view_name;

Examples of Recursive Views in PostgreSQL

Let’s see some examples of recursive views.

Example 1: Calculating Factorials Using Recursive Views

Let’s start with a simple example of calculating factorials using a recursive view:

Query:

CREATE RECURSIVE VIEW fact(n, factorial) AS 
(
 SELECT 1 as n, 5 as factorial
   UNION ALL
 SELECT n+1, factorial*n FROM fact WHERE n < 5
);
SELECT * FROM fact;

In this example:

• The first SELECT statement defines the base case with ‘n = 1′ and ‘factorial = 5′.
• The second SELECT statement performs the recursive computation, incrementing ‘n’ and multiplying the current factorial by ‘n’.
• The UNION ALL combines the results of both queries.

This code will create a view of the factorial of a natural number. which is the same as:

Query:

CREATE VIEW fact AS
WITH RECURSIVE fact(n, factorial) AS
(
  SELECT 1 as n, 5 as factorial
  UNION ALL
  SELECT n + 1, factorial * n FROM fact WHERE n < 5
)
SELECT * FROM fact;

Both will give you the same output. 

Here in this example, we are only considering ‘n=5′. We can print only the last row but here we can see how the iteration and calculation take place. 

Output:

CREATE VIEW
 n | factorial 
---+-----------
 1 |         5
 2 |         5
 3 |        10
 4 |        30
 5 |       120
(5 rows)

Example 2: Generating a Sequence of Numbers Recursively

Let’s take a look at another sample example, which is producing 1 to 10 numbers recursively.

Query:

CREATE RECURSIVE VIEW tens(n) AS 
(
    SELECT 1 as n
 UNION ALL
   SELECT n+1 FROM tens
);
SELECT * FROM tens limit 10;

Output:

 

Example 3: Managing Organizational Hierarchies

Now let’s look at some real-life examples of recursive view, with the help of recursive view we can find the organizational hierarchy. 

Step 1: Create a  table.

Query:

CREATE TABLE emp (
  emp_id INT PRIMARY KEY,
  emp_name VARCHAR(50),
  manager_id INT
);
INSERT INTO emp (emp_id, emp_name, manager_id)
VALUES
(1, 'Onkar', NULL),
(2, 'Isaac', 1),
(3, 'Jack', 1),
(4, 'Aditya', 1),
(5, 'Albert', 1),
(6, 'Alex', 2),
(7, 'Brian', 2),
(8, 'Harry', 3),
(9, 'Paul', 3),
(10, 'Kunal', 4),
(11, 'Pranav', 5);

Step 2: Create a Recursive View to Display the Organizational Hierarchy

This data represent hierarchy, e.g., Isaac is working under Onkar and Alex is working under Isaac. Thus Onkar is on top level. This example will give us results of which employee is working at which level.

Query:

CREATE RECURSIVE VIEW subordinates(emp_id, manager_id, emp_name, level) AS
(
  SELECT emp_id, manager_id, emp_name, 0 AS level
  FROM emp
  WHERE manager_id IS NULL
  UNION ALL
  SELECT e.emp_id, e.manager_id, e.emp_name, s.level + 1
  FROM emp e
  INNER JOIN subordinates s ON s.emp_id = e.manager_id
);

SELECT * FROM subordinates ORDER BY level, emp_id;

Output:

Conclusion

Recursive views in PostgreSQL are a powerful feature for handling hierarchical data, generating sequences, and solving other recursive problems. By storing recursive queries as views, you can simplify complex data relationships, making your queries more efficient and easier to manage.