Prędzej czy później większość z nas stanie przed koniecznością przechowywania danych w strukturze drzewiastej. W sieci istnieje wiele przykładów obrazujących to zagadnienie. Poniższy opis ma na celu pokazanie jednego ze sposobów „wyciagania” danych przechowywanych w tejże strukturze za pomocą funkcji connectby. Przykładem struktury drzewiastej jest tzw. adjacency list model:
CREATE TABLE wypowiedz_tab (
id_wypowiedzi bigint NOT NULL,
temat character varying(255),
tresc character varying(255),
id_ojca_wypowiedzi bigint
);
ALTER TABLE ONLY wypowiedz_tab
ADD CONSTRAINT wypowiedz_tab_pkey PRIMARY KEY (id_wypowiedzi);
ALTER TABLE ONLY wypowiedz_tab
ADD CONSTRAINT wypowiedz_tab_fkey FOREIGN KEY (id_ojca_wypowiedzi) REFERENCES wypowiedz_tab(id_wypowiedzi);
id_wypowiedzi bigint NOT NULL,
temat character varying(255),
tresc character varying(255),
id_ojca_wypowiedzi bigint
);
ALTER TABLE ONLY wypowiedz_tab
ADD CONSTRAINT wypowiedz_tab_pkey PRIMARY KEY (id_wypowiedzi);
ALTER TABLE ONLY wypowiedz_tab
ADD CONSTRAINT wypowiedz_tab_fkey FOREIGN KEY (id_ojca_wypowiedzi) REFERENCES wypowiedz_tab(id_wypowiedzi);
Powyższa tabela zawiera wypowiedzi umieszczane na grupie dyskusyjnej. Jeśli id_ojca_wypowiedzi posiada wartość różną od null znaczy to, iż wypowiedź ta jest odpowiedzią . W innym wypadku mamy do czynienia z wypowiedzią rozpoczynającą nową dyskusję.
Po zapełnieniu tabeli danymi chcielibyśmy w łatwy sposób pobierać dane bez względu na jakim znajdujemy sie poziomie w hierarchii naszego drzewa. Serwer baz danych PostgreSQL oferuje do tego zadania gotową funkcję connectby, znajdującą się w pliku share\contrib\tablefunc.sql. Składnia funkcji jest nastepująca:
connectby('nazwa_tabeli','nazwa_pola_PK','nazwa_pola_FK', 'wartość_pola_PK' , głębokość)
gdzie głębokość oznacza głębokość przeszukiwania.
Po zapełnieniu tabeli danymi chcielibyśmy w łatwy sposób pobierać dane bez względu na jakim znajdujemy sie poziomie w hierarchii naszego drzewa. Serwer baz danych PostgreSQL oferuje do tego zadania gotową funkcję connectby, znajdującą się w pliku share\contrib\tablefunc.sql. Składnia funkcji jest nastepująca:
connectby('nazwa_tabeli','nazwa_pola_PK','nazwa_pola_FK', 'wartość_pola_PK' , głębokość)
gdzie głębokość oznacza głębokość przeszukiwania.
W przypadku gdy argument 'głębokość' ma wartość 0, oznacza to, iż zostanie zwrócony rekord od którego rozpoczęto przeszukiwanie oraz wszystkie względem niego rekordy potomne.
Przed przejściem do następnego etapu należy utworzyć funkcję:
CREATE OR REPLACE FUNCTION connectby(text,text,text,text,int)
RETURNS setof record
AS '$libdir/tablefunc','connectby_text'
LANGUAGE C STABLE STRICT;
Przed przejściem do następnego etapu należy utworzyć funkcję:
CREATE OR REPLACE FUNCTION connectby(text,text,text,text,int)
RETURNS setof record
AS '$libdir/tablefunc','connectby_text'
LANGUAGE C STABLE STRICT;
Przykład zapytania realizującego pobieranie danych za pomocą funkcji connectby:
SELECT * FROM connectby('wypowiedz_tab','id_wypowiedzi','id_ojca_wypowiedzi', '2',0) AS t(keyid bigint, parent_keyid bigint, level int);
Należy zaznaczyć, iż typy keyid i parent_keyid nie mogą się różnić. W powyższym przypadku jest to bigint. Dla tabeli, gdzie wartości klucza głwównego i klucza obcego byłyby tekstem zapytanie wyglądałoby następująco:
SELECT * FROM connectby('wypowiedz_tab','id_wypowiedzi','id_ojca_wypowiedzi', '2',0) AS t(keyid text, parent_keyid text, level int);
Jeśli interesuje nas zwrócenie w wyniku wszystkich kolumn naszej tabeli należy użyć złączenia:
SELECT tt.* FROM connectby('wypowiedz_tab','id_wypowiedzi','id_ojca_wypowiedzi', '2',0) AS t(keyid text, parent_keyid text, level int), wypowiedz_tab as tt where t.keyid=tt.id_wypowiedzi;
W katalogu doc\contrib\README.tablefunc serwera PostgreSQL znajduje się dokładniejszy opis funkcji connectby uwzględniający uzyskanie dodatkowej funkcjonalności, takiej jak np. sortowanie rekordów potomnych.
