Recursive SQL

I was reading Graeme Birchall's SQL Cookbook at 
http://ourworld.compuserve.com/homepages/Graeme_Birchall/HTM_COOK.HTM
and came across an *amazing* technique called recursive SQL.

It's a way to traverse tree-like structures with a single SQL statement. 
Bizarre stuff--I didn't think this was possible.

Anyway, the technique depends upon being able to create a temporary table 
where some of the rows are SELECTed from that very table during its 
creation. Essentially, you fill the table with some starting conditions and 
then use a UNION ALL to keep adding in the new data after each recursive 
pass. Take a look at page 140 in Graeme's book for more info.

I tried this in Postgresql without success. I get syntax errors trying to 
create the temporary table. Here's some code derived from Graeme's cookbook:

create table hierarchy (
pkey char(3) not null,
ckey char(3) not null,
num int4,
primary key(pkey, ckey));

copy hierarchy from stdin;
AAA    BBB    1
AAA    CCC    5
AAA    DDD    20
CCC    EEE    33
DDD    EEE    44
DDD    FFF    5
FFF    GGG    5
\.

Here's my attempt to write recursive SQL code to find the children of 'AAA':

create temporary table parent (pkey, ckey) as
select pkey, ckey from hierarchy where pkey = 'AAA'
union all
select c.pkey, c.ckey from hierarchy c, parent p where p.ckey = c.ckey;

select pkey, ckey from parent;

It appears that Postgresql doesn't like a union inside the create statement. 
Beyond that, I'm wondering if this technique would even work in Postgresql 
if it wasn't designed to handle recursive SQL.

Any thoughts?

Andy Turk
andy_turk@hotmail.com

______________________________________________________
Get Your Private, Free Email at http://www.hotmail.com