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
Message-ID: <003101c2bcd3$720ab1a0$6700a8c0@mattspc> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: cobalt@bearfruit.org, jasiek@klaster.net
Subject: Re: lost on self joins
In-Reply-To: <003101c2bcd3$720ab1a0$6700a8c0@mattspc>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox