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

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

Instruction

Great. Just as you can have multiple columns in your CTE, you can also have multiple CTEs, some of which may be recursive, others not. 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 the example, we first create a list of the first 5 prime numbers, and then we create a recursive CTE that calculates multiples of these prime numbers. Note that the word RECURSIVE is always used before the first CTE, even if that query 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 made from those letters that have at most 5 letters. The shortest word should be an empty word ('').

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.