Funkcja do szukania wierszy potomnych w PostgreSQL-u

Jakiś czas temu spotkałam się z problemem szukania wierszy, które były 'dziećmi' innego wiersza. Najczęstszym przykładem jest firma, która może mieć podfirmy albo sama może być podfirmą.
Gdy struktura takiej firmy jest duża, wyciąganie każdego poziomu drzewa tej struktury, może być dość uciążliwe. Dlatego stworzyłam funkcję, która zwróci wszystkie ID wierszy dzieci i ID rodzica.

Poniższy diagram przedstawia testową strukturę drzewa rodzic-dzieci.

Przygotowanie danych testowych:

postgres@test(localhost) # create table test5 (id integer, parent_id integer, name varchar(25));
CREATE TABLE
postgres@test(localhost) # insert into test5 (id, parent_id, name) values (1,null, 'id: 1, parent: null'), (2, null, 'id: 2, parent: null'), (3, null, 'id: 3, parent: null');
INSERT 0 3
postgres@test(localhost) # insert into test5 (id, parent_id, name) values (4,1, 'id: 4, parent: 1'), (5, 1, 'id: 5, parent: 1'), (6, 2, 'id: 6, parent: 2'), (7, 2, 'id: 7, parent: 2'), (8, 3, 'id: 8, parent: 3'), (9, 3, 'id: 9, parent: 3'),(10, 3, 'id: 10, parent: 3');
INSERT 0 7
postgres@test(localhost) # insert into test5 (id, parent_id, name) values (11,4, 'id: 11, parent: 4'), (12, 4, 'id: 12, parent: 4'), (13, 6, 'id: 13, parent: 6'), (14, 7, 'id: 14, parent: 7'), (15, 7, 'id: 15, parent: 7'), (16, 9, 'id: 16, parent: 9');
INSERT 0 6
postgres@test(localhost) # insert into test5 (id, parent_id, name) values (17,13, 'id: 17, parent: 13'), (18, 15, 'id: 18, parent: 15'), (19, 15, 'id: 19, parent: 15');
INSERT 0 3
postgres@test(localhost) # insert into test5 (id, parent_id, name) values (20,17, 'id: 20, parent: 17'), (21, 18, 'id: 21, parent: 18'), (22, 18, 'id: 22, parent: 18');
INSERT 0 3
postgres@test(localhost) # insert into test5 (id, parent_id, name) values (23,22, 'id: 23, parent: 22');
INSERT 0 1
postgres@test(localhost) # \i /home/ela/get_childern_by_root.sql
DROP FUNCTION
CREATE FUNCTION

Funkcji wymaga tylko jednego argumentu: ID rodzica. Wszystkie operacje wykonywane są na tablicach:

• zmienna := ARRAY(select_statement) - przypisanie do zmiennej tablicy, której dane są wynikiem działania select_statement
• zmienna INTEGER[] - deklaracja zmiennej typu tablica integer-ów
• SELECT .... WHERE parent_id = ANY(my_array) - szukanie wierszy gdzie parent_id jest jedną z wartości w tablicy my_array
• funkcja array_cat - łączenie dwóch tablic
• funkcja array_dims - tekstowa reprezentacja rozmiaru tabeli

Tablice są bardzo przydatne w programowaniu w PostgreSQL-a, dlatego zachęcam do czytania dokumentacji: http://www.postgresql.org/docs/9.1/static/functions-array.html

Przejdziemy do samej funkcji:


DROP FUNCTION IF EXISTS get_childern_by_root(INTEGER);

CREATE OR REPLACE FUNCTION get_childern_by_root(INTEGER) RETURNS integer[] AS $$
DECLARE 
 update_state BOOLEAN DEFAULT true;
 flag BOOLEAN DEFAULT true;
 children_count INTEGER DEFAULT 0;
 root_id ALIAS FOR $1;
 children_ids INTEGER[];
 level_row_ids INTEGER[];
 temp_level_row_ids INTEGER[];
BEGIN
 level_row_ids := ARRAY(SELECT root_id);
 children_ids := ARRAY(SELECT root_id);

 WHILE flag LOOP
 BEGIN
  RAISE NOTICE 'Elements: %',level_row_ids;
  SELECT INTO children_count count(*) FROM test5 WHERE parent_id = ANY(level_row_ids);
  -- RAISE NOTICE 'Elements count: %',children_count;
  IF children_count > 0 THEN
   temp_level_row_ids := level_row_ids;
   level_row_ids := ARRAY(SELECT id FROM test5 WHERE parent_id = ANY(temp_level_row_ids));
   children_ids := array_cat(children_ids,level_row_ids);
  ELSE
   flag := false;
  END IF;
 EXCEPTION
  WHEN others THEN
  RAISE NOTICE 'ERROR, sql code: %, sql error message %', SQLSTATE,SQLERRM;
  RETURN children_ids;
 END;
 END LOOP;

 BEGIN
  RAISE NOTICE 'Founded % ROWS', array_dims(children_ids);
 EXCEPTION
  WHEN others THEN
  RAISE NOTICE 'ERROR, sql code: %, sql error message %', SQLSTATE,SQLERRM;
  RETURN children_ids;
 END;


 RETURN children_ids;
END;
$$ LANGUAGE plpgsql;

Testujemy funkcje sprawdzając wynik z diagramem wyżej:

postgres@test(localhost) # select get_childern_by_root(2);
NOTICE:  Elements: {2}
NOTICE:  Elements: {6,7}
NOTICE:  Elements: {13,14,15}
NOTICE:  Elements: {17,18,19}
NOTICE:  Elements: {20,21,22}
NOTICE:  Elements: {23}
NOTICE:  Founded [1:13] ROWS
         get_childern_by_root          
---------------------------------------
 {2,6,7,13,14,15,17,18,19,20,21,22,23}
(1 row)

postgres@test(localhost) # select get_childern_by_root(1);
NOTICE:  Elements: {1}
NOTICE:  Elements: {4,5}
NOTICE:  Elements: {11,12}
NOTICE:  Founded [1:5] ROWS
 get_childern_by_root 
----------------------
 {1,4,5,11,12}
(1 row)

postgres@test(localhost) # select get_childern_by_root(11);
NOTICE:  Elements: {11}
NOTICE:  Founded [1:1] ROWS
 get_childern_by_root 
----------------------
 {11}
(1 row)

postgres@test(localhost) # select get_childern_by_root(15);
NOTICE:  Elements: {15}
NOTICE:  Elements: {18,19}
NOTICE:  Elements: {21,22}
NOTICE:  Elements: {23}
NOTICE:  Founded [1:6] ROWS
 get_childern_by_root 
----------------------
 {15,18,19,21,22,23}
(1 row)

postgres@test(localhost) # select get_childern_by_root(3);
NOTICE:  Elements: {3}
NOTICE:  Elements: {8,9,10}
NOTICE:  Elements: {16}
NOTICE:  Founded [1:5] ROWS
 get_childern_by_root 
----------------------
 {3,8,9,10,16}
(1 row)

Mam nadzieję, że przyda się wam może kiedyś ta prosta funkcja.