CODEWITHSUNDEEP
databasepostgresqlrecursion
Synchrowave
Synchrowave

Reputation: 89

How does this recursion repeat itself?

I have a question about some code.

I have a relation that is called comedians. It has the attribute comedian and preceding comedian. So the first comedian say Bob, has null in his field for preceding comedian. My question is, how does this code repeat until all child instances are found? I just can not wrap my head around it.

I know that the first part: the one part before UNION ALL selects all parent elements, so all comedians that have no comedians that performed before them (preceding comedian), but how can all the other comedians, under the parent be chosen? What makes it recursive?

with recursive tree as (
  select company, comedian, preceding_comedian, 1 as level
  from the_table
  where preceding_comedian is null
  union all
  select ch.company, ch.comedian, ch.preceding_comedian, p.level + 1
  from the_table ch
    join tree p on ch.preceding_comedian = p.comedian
)

Upvotes: 1

Views: 32

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 246718

  • First, the non-recursive part of the query is performed:

    select company, comedian, preceding_comedian, 1 as level
from the_table
where preceding_comedian is null

    and the result is put in a “work table”.

  • Then the recursive part of the query is performed, where the work table is substituted for the recursive CTE:

    select ch.company, ch.comedian, ch.preceding_comedian, p.level + 1
from the_table ch
join <work-table> p on ch.preceding_comedian = p.comedian

    The result is added to the work table (if UNION is used instead of UNION ALL, duplicates are removed in the result).

  • The second step is repeated until the work table does not change any more.

The resulting work table is the result of the CTE.

So it is actually not so much a recursive, but an “iterative” CTE.

Upvotes: 1

Related Questions