Re: Trees: maintaining pathnames - Mailing list pgsql-sql

On 17 Nov 2002 at 11:39, Dan Langille wrote:

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

I am now trying another method, which involves the use of a cache 
table.  In short, we store the pathname in another table.

create table tree_pathnames (   id int4 not null,   pathname text not null,   primary key(id),   foreign key (id)
referencestree(id)    on delete cascade on update cascade
 
);

I populated this table with the following:
  insert into tree_pathnames select id, pathname from tree;

My next task was to create a function which would cascade a change to 
tree.name throughout tree_pathname.  Here is what I came up with:

create or replace function tree_pathname_set_children(int4, text) 
returns int as 
'DECLARE
node ALIAS for $1;path ALIAS for $2;children record;
BEGIN    FOR children IN SELECT ep.id, ep.pathname, e.name                      FROM element_pathnames ep, element e
                WHERE ep.id       = e.id                       AND e.parent_id = node LOOP
 
--           children.pathname = path ||  ''/'' || children.name;        RAISE NOTICE ''in tree_pathname_set_children
%/%'',path, 
 
children.name ;       UPDATE element_pathnames set pathname = path ||  ''/'' || 
children.name where id = children.id;       perform tree_pathname_set_children(children.id, path ||  ''/'' 
|| children.name);    END LOOP;
    return 0;END;'

language 'plpgsql';

This function is invoked from within the trigger on tree:

create or replace function tree_pathnames() returns opaque as '  DECLARE     parent_pathname   text;     my_pathname
  text;  BEGIN     if old.name <> new.name then        select pathname          into parent_pathname          from
tree_pathnames        where id = new.parent_id;        if found then           my_pathname =  parent_pathname || \'/\'
||new.name;       else           my_pathname = \'/\' || new.name;        end if;
 
        new.pathname = my_pathname;        update tree_pathnames set pathname = my_pathname where id = 
new.id;        perform tree_pathname_set_children(new.id,my_pathname);     end if;
     RETURN new;  END;'

language 'plpgsql';

 drop trigger tree_pathnames on element;
create trigger tree_pathnames before update on element for each row
execute procedure tree_pathnames();

I have done only preliminary testing on this, but it seems to work 
fine for my application.

Comments please.
-- 
Dan Langille : http://www.langille.org/