Matthew Nuzum wrote:
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)
Strange. Do you need this table? Can one file exist in several directories? If not, you can just add "folderid" field into table files.
Now, I'd like to create a view that shows everything in files, as wellWhat would you say about this:
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.
create or replace function parent_dir(varchar,integer) returns varchar as '
DECLARE
curr_name ALIAS for $1;
curr_id ALIAS for $2;
par_name varchar;
par_id integer;
begin
select into par_name,par_id foldername,parentid from folders where folderid=curr_id;
if not found or par_name is null then
--finish
return curr_name;
else
--find upper folder
return parent_dir(par_name || ''/'' || curr_name,par_id);
end if;
end;
' LANGUAGE 'plpgsql';
Using:
select parent_dir('',folderid) as fullpath...;
or select parent_dir(filename,folderid) as fullfilename...;
Your query would look like this:
SELECT files.*,
parent_dir('',folderid) as fullfoldername
FROM files f join files_folders ff using (fileid);
Regards,
Tomasz Myrta
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster
