From: "Dan Langille" <dan@langille.org>
To: pgsql-sql@postgresql.org
Date: Fri, 22 Nov 2002 17:17:05 -0500
MIME-Version: 1.0
Subject: Re: Trees: maintaining pathnames
Message-ID: <3DDE6691.19184.C4BEF7D4@localhost>
In-reply-to: <20021117112520.C8127-200000@nezlok.unixathome.org>
Content-type: text/plain; charset=US-ASCII
Content-transfer-encoding: 7BIT
Content-description: Mail message body

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) references tree(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/