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(at)hotmail(dot)com

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