WITH RECURSIVE defines a CTE that references itself: an anchor part supplies the starting rows, then a recursive part produces new ones from the previous ones, until there are none left. It is used to generate series and walk hierarchies. A stop condition prevents infinite loops.
Example
WITH RECURSIVE n(i) AS (
SELECT 1 UNION ALL SELECT i + 1 FROM n WHERE i < 5
)
SELECT i FROM n;