From: greg@turnstep.com
To: pgsql-sql@postgresql.org
Cc: dan@langille.org
Cc: dan@langille.org
Subject: Re: Trees: maintaining pathnames
Date: Mon, 18 Nov 2002 01:09:29 -0000
Message-ID: <51084baa78b6c787d79599a9a04ee7ea@biglumber.com>


-----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.

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)


Greg Sabino Mullane  greg@turnstep.com
PGP Key: 0x14964AC8 200211172015

-----BEGIN PGP SIGNATURE-----
Comment: http://www.turnstep.com/pgp.html

iD8DBQE92D9RvJuQZxSWSsgRAn2oAKDyIcrtgB8v1fAMY3B/ITKZ+lBlYgCfXRMe
W/xntabEsfuEdseo44cAXbY=
=MANm
-----END PGP SIGNATURE-----