From: "Matthew Nuzum" <cobalt@bearfruit.org>
To: "'Tomasz Myrta'" <jasiek@klaster.net>
Cc: <pgsql-sql@postgresql.org>
Subject: Re: lost on self joins
Date: Wed, 15 Jan 2003 15:19:38 -0500
Organization: Bearfruit.org
Message-ID: <003101c2bcd3$720ab1a0$6700a8c0@mattspc>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: 7bit
Importance: Normal
In-Reply-To: <3E25BDAF.8060109@klaster.net>

> -----Original Message-----
> From: Tomasz Myrta [mailto:jasiek@klaster.net]
> Sent: Wednesday, January 15, 2003 3:00 PM
> To: Matthew Nuzum
> Cc: pgsql-sql@postgresql.org
> Subject: Re: [SQL] lost on self joins
> >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.

Good point.  No, it can't exist in multiple folders, so I guess it's
overkill to do a many to many here.  Thanks for the moment of clarity.

> 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);

Well, I guess I would say Thanks!  You make it look so easy.

As I was planning this in my mind, I didn't have the equivalent of your
first parameter to parent_dir, but now I see it's necessary to get the
full path when you recurse up the folder hierarchy.

Thanks a lot for your response.

Matthew Nuzum
www.bearfruit.org
cobalt@bearfruit.org