Re: How to represent a tree-structure in a relational database

somebody already showed table structure, but i'll ad some more code to this:

table:

CREATE TABLE groups (
id INT4 NOT NULL DEFAULT NEXTVAL('groups_seq'),
parent_id INT4 NOT NULL DEFAULT 0,
name TEXT NOT NULL DEFAULT '',
active BOOL NOT NULL DEFAULT 't'::bool,
PRIMARY KEY (id)
);
INSERT INTO groups (id) VALUES (0);
ALTER TABLE groups ADD FOREIGN KEY (parent_id ) REFERENCES groups (id);
CREATE UNIQUE INDEX groups_pn_u ON groups (parent_id, name, active);

at this point it seems to be pretty easy and obvious.
in my case i got to the point that i needed some more info about the branch of
tree. so i wrote:

REATE function getgrouppath(int4, text) returns text as '
DECLARE
sep ALIAS FOR $2;
aid int4;
wynik TEXT;
temp RECORD;
b BOOL;
BEGIN
b:=''t'';
wynik:='''';
aid:=$1;
while b loop
SELECT name, parent_id INTO temp FROM groups WHERE id=aid;
IF NOT FOUND THEN
return wynik;
END IF;
if wynik = '''' THEN
wynik:=temp.name;
else
wynik:=temp.name||sep||wynik;
END if;
IF temp.parent_id = 0 THEN
b:=''f'';
ELSE
aid:=temp.parent_id;
END if;
end loop;
return wynik;
END;
' language 'plpgsql';

(sorry for polish variable names)
this function does one nice trick
when having structure like:
=> select id, parent_id, name, active from groups;
id | parent_id | name | active
----+-----------+----------------------+--------
0 | 0 | | t
1 | 0 | RTV | t
2 | 0 | AGD | t
3 | 0 | MP3 | t
4 | 1 | Audio | t
5 | 2 | Lodówki | t
6 | 2 | Kuchenki Mikrofalowe | t
7 | 4 | Sony | t
8 | 4 | Panasonic | t
(9 rows)

i can:
=> select id, parent_id, name, active, getgrouppath(id, '/') from
groups;
id | parent_id | name | active | getgrouppath
----+-----------+----------------------+--------+--------------------------
0 | 0 | | t |
1 | 0 | RTV | t | RTV
2 | 0 | AGD | t | AGD
3 | 0 | MP3 | t | MP3
4 | 1 | Audio | t | RTV/Audio
5 | 2 | Lodówki | t | AGD/Lodówki
6 | 2 | Kuchenki Mikrofalowe | t | AGD/Kuchenki Mikrofalowe
7 | 4 | Sony | t | RTV/Audio/Sony
8 | 4 | Panasonic | t | RTV/Audio/Panasonic

since for some reasons (indenting) i needed the level of branch i wrote:

CREATE FUNCTION grouplevel(int4) returns int4 AS '
DECLARE
baseid ALIAS FOR $1;
currid INT4;
reply INT4;
BEGIN
reply:=1;
if baseid = 0 then return 0; END if;
SELECT parent_id INTO currid FROM groups where id=baseid;
while currid>0 loop
reply:=reply+1;
SELECT parent_id INTO currid FROM groups where id=currid;
END loop;
return reply;
END;
' language 'plpgsql';

which also seems pretty obvious.

to be complete i wrote two triggers which made me happy:

CREATE FUNCTION trg_recurs_act_g() RETURNS OPAQUE AS '
BEGIN
IF NEW.active=''f''::bool and OLD.active=''t''::bool THEN
UPDATE articles SET active=''f''::bool WHERE group_id=NEW.id;
UPDATE groups SET active=''f''::bool WHERE parent_id=NEW.id and id<>0;
ELSE
IF NEW.active=''t''::bool and OLD.active=''f''::bool AND NEW.id<>0 THEN
UPDATE groups SET active=''t''::bool WHERE id=NEW.parent_id;
END IF;
END IF;
RETURN NEW;
END;
' LANGUAGE 'plpgsql';

CREATE FUNCTION trg_recurs_act_a() RETURNS OPAQUE AS '
BEGIN
IF NEW.active=''t''::bool and OLD.active=''f''::bool THEN
UPDATE groups SET active=''t''::bool WHERE id=NEW.group_id;
END IF;
RETURN NEW;
END;
' LANGUAGE 'plpgsql';

CREATE TRIGGER groups_update_trg BEFORE UPDATE ON groups FOR EACH ROW EXECUTE PROCEDURE trg_recurs_act_g();
CREATE TRIGGER articles_update_trg BEFORE UPDATE ON articles FOR EACH ROW EXECUTE PROCEDURE trg_recurs_act_a();

as you can see those triggers use article table which structure is not
important at this moment (let's assume it has id, group_id, name and active).

i hope this code will help you a bit.

depesz

--
hubert depesz lubaczewski
------------------------------------------------------------------------
najwspanialszą rzeczą jaką dało nam nowoczesne społeczeństwo,
jest niesamowita wręcz łatwość unikania kontaktów z nim ...