WITH RECUSIVE patches 0717 - Mailing list pgsql-hackers

Hi,

Here is the lastest WITH RECURSIVE patches against CVS HEAD created by
Yoshiyuki Asaba and minor corrections by Tatsuo Ishii. (David Fetter's
psql help patches are not included. It seems his git repository has
gone).

This version implements:

- detect certain queries those are not valid acroding to the standard

I also include erroneous query examples created by Yoshiyuki (probably
will become part of regression tests).

Remaining problmes are:

1) sort query names acording to the dependency
2) planner always estimate 0 cost for recursion plans
3) add regression tests

For 1), I have proposed we limit query names to 1, in another word do
not allow mutually recursive queries. For 2) there's no good idea to
solve it, so I suggest leave it as it is now.

For 3) I will generate regression tests as soon as possible.

So the patches seem to be almost ready to commit IMO.
--
Tatsuo Ishii
SRA OSS, Inc. Japan
-- 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;