Re: lost on self joins

> -----Original Message-----
> From: Tomasz Myrta [mailto:jasiek(at)klaster(dot)net]
> Sent: Wednesday, January 15, 2003 3:00 PM
> To: Matthew Nuzum
> Cc: pgsql-sql(at)postgresql(dot)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(at)bearfruit(dot)org