|
원본출처 : http://www.brasileiro.net/postgres/cookbook/view-one-recipe.adp?recipe_id=15
Submitted on: 03-16-2001
Description:
This is a solution I created when I needed a hierarchicalcategory tree with ability to easily get the number of items in each category, get the full name from the tree root etc.
Code:
The pa_categories table is as follows:
Create table pa_categories (
category_id serial NOT NULL,
name varchar(100) NOT NULL,
code varchar(50) NOT NULL,
parent_id Integer NOT NULL,
Primary Key (category_id)
);
The functions return
- full category name (with its parents), that is also hyperlinked,
- a trigger to calculate the code - it's similar to the 160 encoded tree structure, that was mentioned at the OpenACS bboards, but much more primitive - i do not expect to have this many rows with categories. The code consists of category_ids from the root (separated by #, though there might be a better sign).
The tree leaves of a certain root may be obtained by a string comparison (ie. for code '0#1#124#80' SQL would be
WHERE code LIKE '0#1#124#80').
This code is not considered finished, and I would appreciate all comments.
Known problems: when using different locale than the default 'C' with PG, optimalization of LIKE and REGEXP searches is turned off, forcing a Seq scan)
CREATE FUNCTION full_category_name(INT) RETURNS VARCHAR AS'
DECLARE v_category_id ALIAS FOR $1;
DECLARE tmp_record RECORD;
DECLARE tmp_id VARCHAR;
DECLARE tmp_code VARCHAR;
BEGIN
tmp_code:='''';
SELECT INTO tmp_record * FROM pa_categories WHERE category_id=v_category_id;
IF NOT FOUND THEN
RETURN ''''::varchar;
END IF;
IF tmp_record.category_id=0 THEN
RETURN tmp_record.name;
END IF;
tmp_id:=full_category_name(tmp_record.parent_id);
IF tmp_record.category_id<>0 THEN
tmp_code:=tmp_id::varchar || '' / '' || tmp_record.name::varchar || '''';
END IF;
RETURN tmp_code;
END;
' LANGUAGE 'plpgsql';
CREATE FUNCTION category_code(INT) RETURNS VARCHAR AS '
DECLARE v_category_id ALIAS FOR $1;
DECLARE tmp_record RECORD;
DECLARE tmp_id VARCHAR;
DECLARE tmp_code VARCHAR;
BEGIN
tmp_code:='''';
SELECT INTO tmp_record * FROM pa_categories WHERE category_id=v_category_id;
IF NOT FOUND THEN
RETURN ''0''::varchar;
END IF;
IF tmp_record.category_id=0 THEN
RETURN tmp_record.category_id;
END IF;
tmp_id:=category_code(tmp_record.parent_id);
IF tmp_record.category_id<>0 THEN
tmp_code:=tmp_id::varchar || ''#'' || tmp_record.category_id::varchar;
END IF;
RETURN tmp_code;
END;
' LANGUAGE 'plpgsql';
CREATE FUNCTION t_code_kategorii() RETURNS OPAQUE AS '
BEGIN
NEW.code:=category_code(NEW.parent_id)||''#''|| NEW.category_id;
RETURN NEW;
END;
' LANGUAGE 'plpgsql';
CREATE TRIGGER code_kategorii_tr
BEFORE INSERT OR UPDATE ON pa_kategorie
FOR EACH ROW EXECUTE PROCEDURE t_code_kategorii();
Code by Grzegorz Mucha - feel free to use it!
|
