Message-ID: <3E25BDAF.8060109@klaster.net>
Date: Wed, 15 Jan 2003 20:59:43 +0100
From: Tomasz Myrta <jasiek@klaster.net>
User-Agent: Mozilla/5.0 (Windows; U; Win 9x 4.90; en-US;
	rv:1.2b) Gecko/20021016
MIME-Version: 1.0
To: Matthew Nuzum <cobalt@bearfruit.org>
Cc: pgsql-sql@postgresql.org
Subject: Re: lost on self joins
References: <002d01c2bcc4$17e487a0$6700a8c0@mattspc>
In-Reply-To: <002d01c2bcc4$17e487a0$6700a8c0@mattspc>
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit

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