From: Dan Langille <dan@langille.org>
To: greg@turnstep.com
Cc: pgsql-sql@postgresql.org
Subject: Re: Trees: maintaining pathnames
Date: Mon, 18 Nov 2002 09:32:42 -0500
Message-ID: <3DD8B3BA.13415.AE7BF648@localhost> (raw)
In-Reply-To: <51084baa78b6c787d79599a9a04ee7ea@biglumber.com>
On 18 Nov 2002 at 1:09, greg@turnstep.com wrote:
>
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
> NotDashEscaped: You need GnuPG to verify this message
>
>
> Instead of storing the path in each row, why not let Postgres
> take care of computing it with a function? Then make a view
> and you've got the same table, without all the triggers.
This is how it is now done. I wanted to be able to so this fairly
quickly:
select * from tree where pathname like '/usr/local/%'
in order to get the subtree below a given point. Sorry I didn't
mention that before.
>
> CREATE TABLE tree (
> id INTEGER NOT NULL,
> parent_id INTEGER,
> "name" TEXT NOT NULL,
> PRIMARY KEY (id)
> );
>
>
> INSERT INTO tree VALUES (1,NULL,'');
> INSERT INTO tree VALUES (2,1,'usr');
> INSERT INTO tree VALUES (3,1,'tmp');
> INSERT INTO tree VALUES (4,1,'home');
> INSERT INTO tree VALUES (5,4,'greg');
> INSERT INTO tree VALUES (6,5,'etc');
>
> CREATE OR REPLACE FUNCTION pathname(INTEGER)
> RETURNS TEXT AS
> '
>
> DECLARE
> mypath TEXT;
> myname TEXT;
> myid INTEGER;
>
> BEGIN
>
> SELECT parent_id,name FROM tree WHERE id=$1 INTO myid,mypath;
> IF mypath IS NULL THEN
> RETURN ''No such id\n'';
> END IF;
>
> LOOP
> SELECT parent_id,name FROM tree WHERE id=myid INTO myid,myname;
> mypath := ''/'' || mypath;
> EXIT WHEN myid IS NULL;
> mypath := myname || mypath;
> END LOOP;
>
> RETURN mypath;
>
> END;
> ' LANGUAGE 'plpgsql';
>
> CREATE VIEW mytree AS SELECT *, PATHNAME(id) AS path FROM tree;
>
> SELECT * FROM tree ORDER BY id;
>
> id | parent_id | name
> ----+-----------+------
> 1 | |
> 2 | 1 | usr
> 3 | 1 | tmp
> 4 | 1 | home
> 5 | 4 | greg
> 6 | 5 | etc
> (6 rows)
>
> SELECT * FROM mytree ORDER BY id;
>
> id | parent_id | name | path
> ----+-----------+------+----------------
> 1 | | | /
> 2 | 1 | usr | /usr
> 3 | 1 | tmp | /tmp
> 4 | 1 | home | /home
> 5 | 4 | greg | /home/greg
> 6 | 5 | etc | /home/greg/etc
> (6 rows)
>
> UPDATE tree SET name='users' WHERE id=4;
>
> SELECT * FROM mytree ORDER BY id;
>
> id | parent_id | name | path
> ----+-----------+-------+-----------------
> 1 | | | /
> 2 | 1 | usr | /usr
> 3 | 1 | tmp | /tmp
> 4 | 1 | users | /users
> 5 | 4 | greg | /users/greg
> 6 | 5 | etc | /users/greg/etc
> (6 rows)
That's good. Thank you.
--
Dan Langille : http://www.langille.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: dan@langille.org, greg@turnstep.com
Subject: Re: Trees: maintaining pathnames
In-Reply-To: <3DD8B3BA.13415.AE7BF648@localhost>
* 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