Re: Trees: maintaining pathnames

On 18 Nov 2002 at 1:09, greg(at)turnstep(dot)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/