Updated May 11, 2023
Definition of PostgreSQL Recursive Query
PostgreSQL recursive query provides with a statement which was allows us to use an auxiliary statement in the query. In PostgreSQL, a recursive query, also known as a recursive common table expression, is utilized in various scenarios, including querying hierarchical data such as organizational structures. In PostgreSQL, common table expressions are similar to temporary tables, and they are used during query execution. This will provide a recursive query, which is recursive in nature, and it will fetch the data until the termination condition was not met.
Syntax:
The below syntax shows a recursive query.
WITH RECURSIVE (common table expression name) or table_name AS(
Definition of non-recursive term common table expression
UNION or UNION ALL
Definition of recursive term common table expression
) SELECT * FROM table_name or common table expression name;Parameter:
Below is the parameter description syntax of the recursive query.
- Recursive: Using recursive query and with clause, we are creating temporary tables; the scope of this temporary table is only that query itself.
- CTE name or table name: Common table expression name is specified in its table name on which we have retrieving data.
- Non-recursive term: The PostgreSQL common table expression query defines the result set of common table expressions.
- Recursive term: In PostgreSQL, we can define it by joining one or more common table expressions or query definitions with a non-recursive term.
- UNION: In PostgreSQL, we can use union and union all to join two queries using the recursive query.
- Termination check: We use it when the recursion has reached its end and there are no more rows to return from the iteration.
- Select: We use it to select data from a table or common table expressions in PostgreSQL.
How Recursive Query Works in PostgreSQL?
- Below is the working of a recursive query in PostgreSQL.
- Recursive query in PostgreSQL will work in three parts; the recursive query has four basic steps to execute the query. PostgreSQL will execute the common table expression or recursive query in the following sequence are as follows.
- We define it as the initial value and populate the working table of the recursive query in this step.
- The second step is known as the recursive part. As we can see, the recursive query will referring to print the numbers itself, and it will get the values from the working table, which we have mentioned in the recursive query in PostgreSQL. This recursive function query will continue running until the termination condition is met. It executes the recursive result with Ri as input, and it will result as Ri+1.
- The third step involves executing the actual query to select all values from a table column or common table expressions. This step is repeated until the set is empty, as defined in the second step. It is also called a termination check.
- The fourth step is to return the final result using the UNION and UNION ALL statement.
- The recursive common table expressions and subordinates will define the one non-recursive and one recursive term.
- The non-recursive term will return the base result set of R0. The recursive term will return the direct subordinates.
- The statement in PostgreSQL will provide a way to write an auxiliary statement for use in the large query.
- A common use for common table expressions is as auxiliary statements. Each of these auxiliary statements can select, update, delete, or insert data, and then the main statement can reference them using the WITH clause.
- Recursive query evaluation will contain the non-recursive term’s evaluation; for using UNION, we can discard the duplicate rows.
- The form of a recursive statement with a query in PostgreSQL is always a non-recursive statement, where the recursive statement contains the reference of the queries output.
Examples
We have using the student table to describe an example of a recursive query. Below is the student table structure, and the data that we have used in the example are as follows.
select * from student;
\d+ student;
Example #1 – Recursive Query using UNION
The below example shows that recursive query using UNION.
Code:
WITH RECURSIVE subordinates AS (
SELECT
stud_id,
phone,
stud_name
FROM
student
WHERE
stud_id = 10
UNION
SELECT
s.stud_id,
s.phone,
s.stud_name
FROM
student s
INNER JOIN subordinates sub ON sub.stud_id = s.phone
) SELECT
*
FROM
subordinates;Output:
- In the above example, the recursive common table expression defines the recursive and non-recursive term.
- Non-recursive term will return the base result of R0, which was the stud_id is 10.
Example #2 – Recursive Query using UNION ALL
- The below example shows that recursive query using UNION ALL.
- The non-recursive term will return the base result of R0, which was the stud_id is 5.
Code:
WITH RECURSIVE subordinates AS (
SELECT
stud_id,
phone,
stud_name
FROM
student
WHERE
stud_id = 5
UNION ALL
SELECT
s.stud_id,
s.phone,
s.stud_name
FROM
student s
INNER JOIN subordinates sub ON sub.stud_id = s.phone
) SELECT
*
FROM
subordinates;\Output:
Example #3 – Recursive Query to Retrieve All Data from Table
The below example shows that recursive queries using UNION to retrieve all data from the table.
Code:
WITH RECURSIVE subordinates AS (
SELECT
stud_id,
phone,
stud_name
FROM
Student
UNION
SELECT
s.stud_id,
s.phone,
s.stud_name
FROM
student s
INNER JOIN subordinates sub ON sub.stud_id = s.phone
) SELECT
*
FROM
subordinates;Output:
Recommended Articles
We hope that this EDUCBA information on “PostgreSQL Recursive Query” was beneficial to you. You can view EDUCBA’s recommended articles for more information.
