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
