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