A recursive CTE is a CTE with a subquery that refers to its own name. The WITH clause must begin with WITH RECURSIVE. The recursive CTE subquery has two parts, seed query and recursive query, separated by UNION [ALL] or UNION DISTINCT.
Seed SELECT is executed once to create the initial data subset; recursive SELECT is repeatedly executed to return subsets of data until the complete result set is obtained. Recursion stops when an iteration does not generate any new rows. This is useful to dig into hierarchies (parent/child or part/subpart):
WITH RECURSIVE cte AS(SELECT ... FROM table_name /* seed SELECT */ UNION ALL SELECT ... FROM cte, table_name) /* "recursive" SELECT */ SELECT ... FROM cte;
Suppose you want to print ...
