|
원본 출처 : http://www.brasileiro.net/postgres/cookbook/view-one-recipe.adp?recipe_id=276
Submitted on: 05-04-2001
Description:
This is a set of plpgsql commands to store and view a family tree. (slimmed down to the bare essentials)
fam_relative_is_child determines if one relative is a descendant of another
fam_level simulates the level keyword in Oracle trees
fam_sortkey_gen generates a key that, when used in the order by clause, will sort the tree appropriately
Code:
--
-- Family tree sql-code
--
-- set of plpgsql commands to store and view a family tree
-- slimmed down a bit to be readable in the cookbook!
--
-- Here's a query that would return a list of rows
-- ordered in tree fashion starting with the relative
-- whose relative_id is $root_id
--
-- You would use the 'level' var to indent appropriately
-- select relative_id,
-- fam_relative_name(relative_id) as name,
-- fam_relative_name(spouse) as spouse_name,
-- fam_level(relative_id,$root_id,0) as level,
-- fam_sortkey_gen(relative_id,$root_id) as sortkey,
-- fam_story_avail_p(relative_id) as story_avail_p,
-- fam_photo_avail_p(relative_id) as photo_avail_p
-- from family_relatives
-- where fam_relative_is_child(relative_id, $root_id) = 't' or
-- relative_id = :root
-- order by sortkey
--
--
-- @author Vinod Kurup (vkurup@massmed.org)
-- @creation-date 2001-02-16
--
-- Feel free to use to your heart's content
create table family_relatives (
relative_id integer primary key,
spouse integer
references family_relatives(relative_id),
mother integer
references family_relatives(relative_id),
father integer
references family_relatives(relative_id),
birth_year integer,
birth_date date,
first_names varchar(100),
last_name varchar(100),
sex char(1)
check (sex in ('m','f')),
check ( birth_year is not null or birth_date is not null)
);
-- This function accepts 2 relative_id's and
-- returns 't' if the first_id is a descendant of the second_id
create function fam_relative_is_child(integer,integer)
returns varchar as '
declare
rel_id alias for $1;
ancestor_id alias for $2;
mother_id integer;
father_id integer;
BEGIN
select into mother_id mother from family_relatives where relative_id = rel_id;
select into father_id father from family_relatives where relative_id = rel_id;
IF (mother_id = ancestor_id) or (father_id = ancestor_id) THEN
return ''t'';
ELSE
IF (mother_id is null) and (father_id is null) THEN
return ''f'';
ELSE
IF fam_relative_is_child(mother_id,ancestor_id) = ''f'' THEN
return fam_relative_is_child(father_id,ancestor_id);
ELSE
return ''t'';
END IF;
END IF;
END IF;
END;
' language 'plpgsql';
-- fam_level simulates the 'level' keyword in Oracle connect by statements
-- it accepts 3 parameters (relative_id, ancestor_id, dummy value)
-- it returns the number of generations separating relative_id and
-- ancestor_id (assuming that you send 0 as the dummy_value
create function fam_level(integer,integer,integer)
returns integer as '
declare
rel_id alias for $1;
ancestor_id alias for $2;
level alias for $3;
mother_id integer;
father_id integer;
tmp_level integer;
BEGIN
select into mother_id mother from family_relatives where relative_id = rel_id;
select into father_id father from family_relatives where relative_id = rel_id;
IF (rel_id = ancestor_id) THEN
return level;
ELSE
IF (mother_id is null) and (father_id is null) THEN
return -1;
ELSE
tmp_level := fam_level(coalesce(mother_id,0), ancestor_id,level+1);
IF (tmp_level = -1) THEN
return fam_level(coalesce(father_id,0), ancestor_id,level+1);
ELSE
return tmp_level;
END IF;
END IF;
END IF;
END;
' language 'plpgsql';
-- fam_sortkey_gen generates a key that can be used to sort
-- your results so that they will be arranged in the proper tree order
-- it takes 2 parameters - the current relative_id and
the relative_id at the start of the tree
-- it returns a string
-- uses the year of birth to create a sort key
-- so that rows are ordered by age (within families)
create function fam_sortkey_gen(integer,integer)
returns varchar as '
declare
rel_id alias for $1;
start alias for $2;
fullname varchar;
tmp_fullname varchar;
mother_id integer;
father_id integer;
plsql_val record;
BEGIN
IF rel_id = 0 THEN
return ''X'';
END IF;
select into plsql_val
trim(coalesce(to_char(birth_date, ''YYYY''),to_char(birth_year, ''9999''))) as birthyear,
mother, father
from family_relatives
where relative_id = rel_id;
fullname := plsql_val.birthyear;
mother_id := plsql_val.mother;
father_id := plsql_val.father;
IF rel_id = start THEN
return fullname;
ELSE
IF (mother_id is null) and (father_id is null) THEN
return ''X'';
ELSE
tmp_fullname = fam_sortkey_gen(coalesce(mother_id,0),start);
IF (position(''X'' in tmp_fullname) > 0) and (father_id is not null) THEN
return fam_sortkey_gen(father_id,start) || fullname;
ELSE
return tmp_fullname || fullname;
END IF;
END IF;
END IF;
END;
' language 'plpgsql';
-- not really related to the tree functions
-- simply returns a relative's name (used in example at top of page)
create function fam_relative_name(integer)
returns varchar as '
declare
rel_id alias for $1;
fullname varchar;
BEGIN
select into fullname first_names || '' '' || last_name
from family_relatives
where relative_id = rel_id;
return fullname;
END;
' language 'plpgsql';
|
