waiting for 8.4

I'm a PostgreSQL-User and today i have tested a new and cool feature in the upcoming new release 8.4:

CTE, aka Common Table Expressions. It allows recursive queries.


Imagine, you store your pedigree in a table:

test=# select * from pedigree ;
id |         name         | father | mother
----+----------------------+--------+--------
1 | Guenter Kretschmer   |        |
2 | Christa Kretschmer   |        |
3 | Andreas Kretschmer   |      1 |      2
4 | Anja Kretschmer      |        |
5 | Magdalena Kretschmer |      3 |      4
6 | Katharina Kretschmer |      3 |      4
7 | Tamara Kretschmer    |      3 |      4
(7 rows)

Now you want to see all children, for instance from my father:

with recursive children as (
  select p.*, 1 as level
    from pedigree p where name='Guenter Kretschmer'
  union all
    select p.*, c.level+1
    from pedigree p
      join children c on (c.id=p.father)
) select
  c.name,
  c.level,
  coalesce(father.name,'--unknown--') as father,
  coalesce(mother.name,'--unknown--') as mother
    from children c
      left join pedigree as father on (c.father=father.id)
      left join pedigree as mother on (c.mother=mother.id);


name                  | level | father             |       mother
----------------------+-------+--------------------+--------------------
Guenter Kretschmer    |     1 | --unknown--        | --unknown--
Andreas Kretschmer    |     2 | Guenter Kretschmer | Christa Kretschmer
Magdalena Kretschmer  |     3 | Andreas Kretschmer | Anja Kretschmer
Katharina Kretschmer  |     3 | Andreas Kretschmer | Anja Kretschmer
Tamara Kretschmer     |     3 | Andreas Kretschmer | Anja Kretschmer
(5 rows)

Or you want to see the parents:

with recursive parents as (
select p.*, 1 as level
   from pedigree p where name='Tamara Kretschmer'
union all
select
   p.*,
  parents.level+1
from pedigree p, parents
where p.id=parents.father or p.id=parents.mother
) select
parents.id,
parents.name,
father.name as father,
mother.name as mother
from
 parents
 left join pedigree father on parents.father=father.id
 left join pedigree mother on parents.mother=mother.id;

id  | name               | father             |       mother
----+--------------------+--------------------+--------------------
7   | Tamara Kretschmer  | Andreas Kretschmer | Anja Kretschmer
3   | Andreas Kretschmer | Guenter Kretschmer | Christa Kretschmer
4   | Anja Kretschmer    |                    |
1   | Guenter Kretschmer |                    |
2   | Christa Kretschmer |                    |
(5 rows)