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 [EMAIL PROTECTED] ---------------------------(end of broadcast)--------------------------- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
