postgresql with再帰クエリ

Oracle       CONNECT BY         。 
PostgreSQL8.3    connectby()           。 connectby()     contrib/tablefunc        ，          ，      。   
PostgreSQL8.4      connectby()          ，            。         PostgreSQL8.4          SQL       。 
1. WITH   。 WITH           
    ：
WITH regional_sales AS (
        SELECT region, SUM(amount) AS total_sales
        FROM orders
        GROUP BY region
     ), top_regions AS (
        SELECT region
        FROM regional_sales
        WHERE total_sales > (SELECT SUM(total_sales)/10 FROM regional_sales)
     )
SELECT region,
       product,
       SUM(quantity) AS product_units,
       SUM(amount) AS product_sales
FROM orders
WHERE region IN (SELECT region FROM top_regions)
GROUP BY region, product;
  SQL       （region），   top        。
 
2. WITH RECURSIVE。   RECURSIVE      ，          。 
  1：1 100  
WITH RECURSIVE t(n) AS (
    VALUES (1)
  union   ALL
    SELECT n+1 FROM t WHERE n < 100
)
SELECT sum(n) FROM t;
    ：5050
 
  2：             
WITH RECURSIVE r AS (
       SELECT * FROM tree WHERE id = 1
     union   ALL
       SELECT tree.* FROM tree, r WHERE tree.parent = r.id
     )
   SELECT * FROM r ORDER BY id;
  id | parent
----+--------
  1 |
  2 |      1
  3 |      1
  4 |      3
 
         PostgreSQL     。（ http://www.postgresql.org/docs/8.4/static/queries-with.html ）