From: Dan Langille <dan@langille.org>
To: pgsql-sql@postgresql.org
Subject: Re: Trees: maintaining pathnames
Date: Fri, 22 Nov 2002 17:17:05 -0500
Message-ID: <3DDE6691.19184.C4BEF7D4@localhost> (raw)
In-Reply-To: <20021117112520.C8127-200000@nezlok.unixathome.org>
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/
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: dan@langille.org
Subject: Re: Trees: maintaining pathnames
In-Reply-To: <3DDE6691.19184.C4BEF7D4@localhost>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox