Re: lost on self joins

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 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.

What would you say about this:

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