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

From Matthew Nuzum
Subject Re: lost on self joins
Date
Msg-id 003101c2bcd3$720ab1a0$6700a8c0@mattspc
Whole thread Raw
In response to Re: lost on self joins  (Tomasz Myrta <jasiek@klaster.net>)
Responses Re: lost on self joins
List pgsql-sql
> -----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



pgsql-sql by date:

Previous
From: "Ross J. Reedstrom"
Date:
Subject: Re: sort by relevance
Next
From: Bruno Wolff III
Date:
Subject: Re: RFC: A brief guide to nulls