-- UNION
WITH RECURSIVE x(n) AS (SELECT 1 UNION SELECT n+1 FROM x)
  SELECT * FROM x;

-- INTERSECT
WITH RECURSIVE x(n) AS (SELECT 1 INTERSECT SELECT n+1 FROM x)
  SELECT * FROM x;

WITH RECURSIVE x(n) AS (SELECT 1 INTERSECT ALL SELECT n+1 FROM x)
  SELECT * FROM x;

-- EXCEPT
WITH RECURSIVE x(n) AS (SELECT 1 EXCEPT SELECT n+1 FROM x)
  SELECT * FROM x;

WITH RECURSIVE x(n) AS (SELECT 1 EXCEPT ALL SELECT n+1 FROM x)
  SELECT * FROM x;

-- 再帰項なし
WITH RECURSIVE x(n) AS (SELECT n FROM x)
  SELECT * FROM x;

-- 左側に再帰項がある
WITH RECURSIVE x(n) AS (SELECT n FROM x UNION ALL SELECT 1)
  SELECT * FROM x;

CREATE TEMP TABLE y (a int);
INSERT INTO y SELECT generate_series(1, 10);
-- LEFT JOIN
WITH RECURSIVE x(n) AS (SELECT a FROM y WHERE a = 1 UNION ALL SELECT x.n+1 FROM y LEFT JOIN x ON x.n = y.a where n < 10)
  SELECT * FROM x;

-- RIGHT JOIN
WITH RECURSIVE x(n) AS (SELECT a FROM y WHERE a = 1 UNION ALL SELECT x.n+1 FROM x RIGHT JOIN y ON x.n = y.a where n < 10)
  SELECT * FROM x;

-- FULL JOIN
WITH RECURSIVE x(n) AS (SELECT a FROM y WHERE a = 1 UNION ALL SELECT x.n+1 FROM x FULL JOIN y ON x.n = y.a where n < 10)
  SELECT * FROM x;

-- subquery
WITH RECURSIVE x(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM x
                          WHERE n IN (SELECT * FROM x))
  SELECT * FROM x;

WITH RECURSIVE x(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM x
                          WHERE n = 1 AND n IN (SELECT * FROM x))
  SELECT * FROM x;

-- GROUP BY
WITH RECURSIVE x(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM x GROUP BY n)
  SELECT * FROM x;

-- HAVING
WITH RECURSIVE x(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM x HAVING n < 10)
  SELECT * FROM x;

-- 集約関数
WITH RECURSIVE x(n) AS (SELECT 1 UNION ALL SELECT count(*) FROM x)
  SELECT * FROM x;

WITH RECURSIVE x(n) AS (SELECT 1 UNION ALL SELECT sum(*) FROM x)
  SELECT * FROM x;