Deals Of The Week - hours only!Up to 80% off on all courses and bundles.-Close

Wrap course as a gift

Prepare your gift and double-check it looks just right.

Form validation errors

Recipient's Name:
Recipient's Email:
Your Message (Optional):
Proceed to Checkout

Recursive Queries in PostgreSQL

Introduction
The very basics
9. Multiple CTEs
Infinite looping
Processing graphs with recursive CTEs
Summary

Instruction

Nice work! Just as you can have multiple columns in your CTE, you can also have multiple CTEs in one query, some of which may be recursive. The rule for syntax is as follows: if at least one CTE is recursive, start the whole query with WITH RECURSIVE and then define your CTEs just as you would normally do. Take a look at the example below.

WITH RECURSIVE prime(x) AS (
  SELECT    2
  UNION 
  SELECT    3
  UNION
  SELECT    5
  UNION
  SELECT    7
  UNION
  SELECT   11
),

total(multiply,prime,score) AS (
  SELECT 
    0,
    0,
    0
  UNION 
  SELECT 
    multiply+1,
    x, 
    x * (multiply+1)
  FROM prime, total 
  WHERE multiply < 3
)

SELECT 
  * 
FROM total
ORDER BY multiply, prime;

In this example, we first create a list of the first 5 prime numbers, then we create a recursive CTE that calculates the multiples of these prime numbers. Note that the word RECURSIVE is always used before the first CTE, even if that CTE is not recursive.

Exercise

Look at the template. There is already a single CTE named letters defined for you. Your task is to complete the words CTE so that it generates all possible words (of no more than 5 letters) that can be made from those letters.

Stuck? Here's a hint!

The anchor member should be an empty string ( '' ).

The recursive member should use the word you have so far and a new letter from letters. Use the concatenation symbol ( || ) to join new letters.

Remember about the termination check – use the function length(w) to that end.