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.
--
-- Selected TOC Entries:
--
\connect - pgsql
--
-- TOC Entry ID 3 (OID 15830772)
--
-- Name: "plpgsql_call_handler" () Type: FUNCTION Owner: pgsql
--
CREATE FUNCTION "plpgsql_call_handler" () RETURNS opaque AS '$libdir/plpgsql',
'plpgsql_call_handler' LANGUAGE 'C';
--
-- TOC Entry ID 4 (OID 15830773)
--
-- Name: plpgsql Type: PROCEDURAL LANGUAGE Owner:
--
CREATE TRUSTED PROCEDURAL LANGUAGE 'plpgsql' HANDLER "plpgsql_call_handler"
LANCOMPILER '';
\connect - dan
--
-- TOC Entry ID 6 (OID 15830774)
--
-- Name: "tree_pathname_set" () Type: FUNCTION Owner: dan
--
CREATE 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';
--
-- TOC Entry ID 2 (OID 15832154)
--
-- Name: tree Type: TABLE Owner: dan
--
CREATE TABLE "tree" (
"id" integer NOT NULL,
"parent_id" integer,
"name" text NOT NULL,
"pathname" text NOT NULL,
Constraint "tree_pkey" Primary Key ("id")
);
--
-- TOC Entry ID 5 (OID 15834571)
--
-- Name: "tree_pathname_set_children" () Type: FUNCTION Owner: dan
--
CREATE 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';
--
-- Data for TOC Entry ID 7 (OID 15832154)
--
-- Name: tree Type: TABLE DATA Owner: dan
--
COPY "tree" FROM stdin;
1 \N dan /dan
2 1 ports /dan/ports
3 2 security /dan/ports/security
\.
--
-- TOC Entry ID 8 (OID 15832163)
--
-- Name: tree_pathname_set Type: TRIGGER Owner: dan
--
CREATE TRIGGER "tree_pathname_set" BEFORE INSERT OR UPDATE ON "tree" FOR EACH ROW
EXECUTE PROCEDURE "tree_pathname_set" ();
--
-- TOC Entry ID 9 (OID 15834573)
--
-- Name: tree_pathname_set_children Type: TRIGGER Owner: dan
--
CREATE TRIGGER "tree_pathname_set_children" AFTER INSERT OR UPDATE ON "tree" FOR EACH
ROW EXECUTE PROCEDURE "tree_pathname_set_children" ();
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
