Re: lost on self joins - Mailing list pgsql-sql

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