agora inbox for pgsql-sql@postgresql.org  

help / color / mirror / Atom feed

From: Andy Turk <andy_turk@hotmail.com>
To: pgsql-sql@postgresql.org
Subject: Recursive SQL
Date: Wed, 19 Apr 2000 12:27:46 EDT
Message-ID: <20000419162746.84052.qmail@hotmail.com> (raw)

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

view thread (16+ messages)  latest in thread

Message-ID: <20000419162746.84052.qmail@hotmail.com>
Permalink:  ../20000419162746.84052.qmail@hotmail.com/
Also on:    postgresql.org/message-id/20000419162746.84052.qmail@hotmail.com

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: andy_turk@hotmail.com
  Subject: Re: Recursive SQL
  In-Reply-To: <20000419162746.84052.qmail@hotmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox