Message-ID: <20000419162746.84052.qmail@hotmail.com>
Reply-To: andy_turk@hotmail.com
From: "Andy Turk" <andy_turk@hotmail.com>
To: pgsql-sql@postgresql.org
Subject: Recursive SQL
Date: Wed, 19 Apr 2000 12:27:46 EDT
Mime-Version: 1.0
Content-Type: text/plain; format=flowed

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