about me and my world

Donnerstag, 9. Oktober 2008

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)


Labels