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
DEFAULT0, 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
groupsWHERE 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>0loop     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.idand id<>0;   ELSE     IF NEW.active=''t''::bool and OLD.active=''f''::bool AND NEW.id<>0 THEN
UPDATEgroups 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
nowoczesnespołeczeństwo,     jest niesamowita wręcz łatwość unikania kontaktów z nim ...