Trees: maintaining pathnames - Mailing list pgsql-sql

My existing tree implementation reflects the files contained on disk.  The
full pathname to a particlar file is obtained from the path to the parent
directory.  I am now considering putting this information into a field in
the table.

Attached you will find the pg_dump from my test database (2.4k) if you
want to test with this setup and in case what I have pasted below
contains an error.

Here is the table and the test data:

create table tree(id int not null, parent_id int, name text not null,
pathname text not null, primary key (id));

insert into tree (id, name, pathname) values (1, 'usr', '/usr');
insert into tree (id, name, parent_id, pathname) values (2, 'ports', 1,
'/usr/ports');
insert into tree values (3, 2, 'security', 'test');

select * from tree;

test=# select * from tree;id | parent_id |   name   |      pathname
----+-----------+----------+--------------------- 1 |           | usr      | /usr 2 |         1 | ports    | /usr/ports
3|         2 | security | /usr/ports/security
 
(3 rows)


The goal is to ensure that pathname always contains the correct value.
Here are the functions/triggers which I created in order to attain that
goal.

This function ensures that the pathname is set correctly when a row is
inserted or changed.

create or replace function tree_pathname_set()
returns opaque
as '

DECLARE      parent_pathname   text;
BEGIN       RAISE NOTICE \'into tree_pathname_set with %:%:%\', new.id,
new.name, new.pathname;       select pathname       into parent_pathname       from tree       where id =
new.parent_id;      if found then          new.pathname = parent_pathname || \'/\' || new.name;       else
new.pathname= \'/\' || new.name;       end if;       RETURN new;   END;'
 
language 'plpgsql';\

create trigger tree_pathname_set before insert or update on tree
for each row execute procedure tree_pathname_set();


This function ensures that any childre of a recently modified row are also
kept up to date.

create or replace function tree_pathname_set_children()
returns opaque
as 'BEGIN       RAISE NOTICE \'into tree_pathname_set_children with %:%:%\',
new.id, new.name, new.pathname;
       update tree set pathname = new.pathname || \'/\' || name where
parent_id = new.id;
       RETURN new;   END;'
language 'plpgsql';

create trigger tree_pathname_set_children after insert or update on tree
for each row execute procedure tree_pathname_set_children();

NOTE: the above is "insert or update" but as I typed this I realize that
only update is sufficent.

A change to the top level row is shown below:

test=# update tree set name = 'dan' where id = 1;
NOTICE:  into tree_pathname_set with 1:dan:/usr
NOTICE:  into tree_pathname_set_children with 1:dan:/dan
NOTICE:  into tree_pathname_set with 2:ports:/dan/ports
NOTICE:  into tree_pathname_set_children with 2:ports:/dan/ports
NOTICE:  into tree_pathname_set with 3:security:/dan/ports/security
NOTICE:  into tree_pathname_set_children with
3:security:/dan/ports/security
UPDATE 1
test=# select * from tree;id | parent_id |   name   |      pathname
----+-----------+----------+--------------------- 1 |           | dan      | /dan 2 |         1 | ports    | /dan/ports
3|         2 | security | /dan/ports/security
 
(3 rows)

test=#

Suggestions, comment, open ridicule, most welcome.  thanks.