From: "Matthew Nuzum" <cobalt@bearfruit.org>
To: <pgsql-sql@postgresql.org>
Subject: lost on self joins
Date: Wed, 15 Jan 2003 13:29:47 -0500
Organization: Bearfruit.org
Message-ID: <002d01c2bcc4$17e487a0$6700a8c0@mattspc>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: 7bit
Importance: Normal

Sometimes recursion makes my head spin...

Imagine that I have a database that holds the structure of my
filesystem.  There is a table called files that contains every piece of
info you would ever want to know about a file, including a unique ID
called fileid.
 | files
========
x| fileid
 | filename
 | ...

Then, you have a table called folders which looks like:
 | folders
==========
x| folderid
 | parentid (relates to folders.folderid)
 | foldername

Finaly, a table to allow a many to many join called files_folders
 | files_folders
================
x| ffid
 | folderid (fk to folders.folderid)
 | fileid (fk to files.fileid)

Now, I'd like to create a view that shows everything in files, as well
as the complete path to the file.  However because I don't know how many
levels deep the file is nested, I'm not sure how to get that complete
path.  Here is conceptually what should come out:

 | files_view
==============
x| fileid
 | filename
 | ...
 | full_path

Something that won't work is: 
SELECT files.*, folders.foldername, folders2.foldername 
FROM files, folders, folders folders2, files_folders ff
WHERE files.fileid = ff.fileid 
  AND ff.folderid = folders.folderid 
  AND folders.parentid;

The problem is that files that are not in a folder won't show up, and if
a folder is more than two levels deep it will only show the two highest
levels.

Can anyone suggest a way for me to get the information I need?  I'm very
content to use a simple pl/pgsql function, however I don't know how I'd
use recursion there.

Thanks,

--
Matthew Nuzum
www.bearfruit.org
cobalt@bearfruit.org