Received: from localhost (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with ESMTP id 0A4F34758F1
	for <pgsql-sql@postgresql.org>; Mon, 18 Nov 2002 09:32:41 -0500 (EST)
Received: from bast.unixathome.org (unknown [66.11.174.150])
	by postgresql.org (Postfix) with ESMTP id A2ADD4758E6
	for <pgsql-sql@postgresql.org>; Mon, 18 Nov 2002 09:32:39 -0500 (EST)
Received: from wocker (wocker.unixathome.org [192.168.0.99])
	by bast.unixathome.org (Postfix) with ESMTP
	id DFCFE3F4E; Mon, 18 Nov 2002 09:32:42 -0500 (EST)
From: "Dan Langille" <dan@langille.org>
To: greg@turnstep.com
Date: Mon, 18 Nov 2002 09:32:42 -0500
MIME-Version: 1.0
Subject: Re: Trees: maintaining pathnames
Cc: pgsql-sql@postgresql.org
Message-ID: <3DD8B3BA.13415.AE7BF648@localhost>
In-reply-to: <51084baa78b6c787d79599a9a04ee7ea@biglumber.com>
X-mailer: Pegasus Mail for Windows (v4.02a)
Content-type: text/plain; charset=US-ASCII
Content-transfer-encoding: 7BIT
Content-description: Mail message body
X-Virus-Scanned: by AMaViS new-20020517
X-Archive-Number: 200211/258
X-Sequence-Number: 10400

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/