Definition of PostgreSQL Recursive Query
PostgreSQL recursive query provides with a statement which was allows us to use auxiliary statement in the query. A recursive query is a query in PostgreSQL, referred to as recursive common table expressions; it is used in multiple situations such as querying hierarchical data like organisation structure. The common table expression in PostgreSQL is the same as temporary tables, which was used during running the query. PostgreSQL will provide a recursive query, which was recursive in nature, and it will fetch the data until the termination condition was not met.
Syntax:
Below syntax shows 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: This is defined in PostgreSQL common table expression query, which provided the result set of common table expressions.
- Recursive term: It is defined in PostgreSQL in one or more common table expressions or query definitions join with the non-recursive term.
- UNION: Union and union all is used to join the two queries by using the recursive query in PostgreSQL.
- Termination check: It is used when the recursion stops, and no rows will return from the iteration.
- Select: It is used to select the data from a table or from the 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.
- It is defined as the initial value, and the working table of the recursive query is populated in this step.
- The second step is known as the recursive part. As we can see in 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 query in the recursive function will continue running till the termination condition is met. It executes the recursive result with Ri as input, and it will result as Ri+1.
- The third step is the actual part or query, which was used to select all the values from a column from a table or common table expressions. This step is defined as repeat the second step until the set is not empty. 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.
- With the statement in PostgreSQL will provides the way to write an auxiliary statement for use in the large query.
- An auxiliary statement which is often to be used as common table expressions. Each of the auxiliary statements can be used to select, update, delete or insert, and clause itself to its primary statement.
- 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 of PostgreSQL Recursive Query
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
This is a guide to PostgreSQL Recursive Query. Here we also discuss the definition and how recursive query work in postgresql along with different examples and their code implementation. you may also have a look at the following articles to learn more –
