|
원본출처 : http://www.brasileiro.net/postgres/cookbook/view-one-recipe.adp?recipe_id=297
Submitted on: 05-11-2001
Description:
Another category tree. This one allows a category to have multiple parents.
Code:
Alan Young (alany@subdimension.com)
This code is freely available ... you don't have to make
your stuff open if you don't want to but you probably won't
get the benefit of this community if you do.
The setup goes like so:
create table category (
id serial not null primary key,
shortname not null text,
longname text,
description text
);
create unique index category_shortname on category ( shortname );
insert into table ( id, shortname, longname, description )
values ( 0, 'ROOT', 'ROOT', 'ROOT' );
create table subcategory (
category_id int4 references category ( id ),
parent_id int4 references category ( id )
);
create function validate_subcategory_parent_id () returns opaque as '
begin
if new.parent_id = new.category_id then
raise exception ''Parent ID (parent_id) and Category ID (category_id) cannot be the same.'';
end if;
return new;
end;
' language 'plpgsql';
create trigger validate_subcategory
before insert or update on subcategory
for each row execute procedure validate_subcategory_parent_id ();
I'm not sure how (or if I even need) to check for circular
references (other than the immediate case as above) because
I want to allow the following possibility:
CatA
CatB
CatA
CatC
CatA
I've got four different types of results that I need and
I'm stumped on two of them ... ideas would be appreciated.
1. A category's parent:
SELECT shortname, longname, description
FROM category c, subcategory s
WHERE s.category_id = [category]
AND c.id = s.parent_id
2. All ancestor's of a category:
3. All immediate children of a category:
SELECT shortname, longname, description
FROM category c, subcategory s
WHERE s.parent_id = [category]
AND c.id = s.category_id
4. All descendants of a category:
These would all be created as views. I need to go home so
I must close this, but I will add more as I learn more.
|
