Problem z funkcja rekurencyjna (dosyc dlugie), pojawiaja sie nadmiarowe wywolania... Opcje

[delta9]

Postgres 8.2

mam nastepujaca tabele:

T = (page_id. parent_page_id, title)

title sobie odpuscimy bo nie jest istotne dla problemu (ale pobieram pozniej w zapytaniu)

page_id | parent_page_id
2 | 0
4 | 3
3 | 2
6 | 4
7 | 2
9 | 0
10 | 9

czyli kazde page_id albo nie ma (0) albo posiada rodzica i tworzy sie nam pewna hierachia

chcialbym wyswietlic finalnie takie drzewko:

-2
--3
--7
---4
----6
-9
--10

napisalem funckje rekurencyjna i funkcje opakowujaca ktora zalatwia tabele tymczasowe itd:

funkcja opakowujaca:

[SQL] pobierz, plaintext 
CREATE OR REPLACE FUNCTION get_list_pages_final()
  RETURNS SETOF t_get_list_pages_out AS $BODY$
DECLARE
  result t_get_list_pages_out;
  test1 integer;
  test2 integer;
  q TEXT;
BEGIN -- tabele tymczasowe ...
  q := 'SELECT count(relname) FROM pg_class WHERE relname = 'tt_get_list_pages_temp'';
  execute q INTO test1; IF test1 = 0 then
      raise notice 'tworze tt_get_list_pages_temp';
      EXECUTE 'CREATE TEMP TABLE tt_get_list_pages_temp (page_id INT, depth INT)';
  else raise notice 'NIE TWORZE tt_get_list_pages_temp';
  end IF;
 
  execute 'INSERT INTO tt_get_list_pages_temp VALUES (0,0)';
 
  q := 'SELECT count(relname) FROM pg_class WHERE relname = 'tt_get_list_pages_result'';
  execute q INTO test2; IF test2 = 0 then
      raise notice 'tworze tt_get_list_pages_result';
      EXECUTE 'CREATE TEMP TABLE tt_get_list_pages_result (page_id INT, title TEXT, depth INT)';
  else raise notice 'NIE TWORZE tt_get_list_pages_result';
  end IF;
 
  -- uruchimineie funckji rekurencyjnej
  perform * FROM get_list_pages_rek(0); FOR result IN SELECT * FROM tt_get_list_pages_result LOOP
    RETURN next result;
  end loop;
 
  -- sprzatanie
  TRUNCATE TABLE tt_get_list_pages_temp;
  TRUNCATE TABLE tt_get_list_pages_result;
END;
$BODY$
  LANGUAGE 'plpgsql' VOLATILE;
[SQL] pobierz, plaintext 

i glowna funkcja rekurencyjna:

[SQL] pobierz, plaintext 
CREATE OR REPLACE FUNCTION get_list_pages_rek(_page_id integer)
  RETURNS void AS $BODY$
DECLARE
  root RECORD;
  _depth INT := 0;
  q TEXT;
BEGIN FOR root IN SELECT page_id, title FROM pages WHERE parent_page_id = _page_id LOOP
    raise notice 'root=%',root;
    -- pobranie aktualnego poziomu
    q := 'SELECT depth FROM tt_get_list_pages_temp WHERE page_id = 0'; -- 1 na stale
    execute q INTO _depth; _depth := _depth + 1;
    raise notice 'depth=%',_depth;
    -- update tabeli tymczasowej
    execute 'UPDATE tt_get_list_pages_temp SET depth = ' || quote_literal(_depth) || ' WHERE page_id = 0';
    -- update tabeli wynikow
    execute 'INSERT INTO tt_get_list_pages_result VALUES ( ' || quote_literal(root.page_id) || ', ' || quote_literal(root.title) || ', ' || quote_literal(_depth) ||')';
    -- rekurencyjne wywolanie funkcji
    execute get_list_pages_rek(root.page_id);
  END LOOP;
  raise notice 'ITERACJA ZAKONCZONA';
  execute 'UPDATE tt_get_list_pages_temp SET depth = 1 WHERE page_id = 0';
END;
 
$BODY$
  LANGUAGE 'plpgsql' VOLATILE;
[SQL] pobierz, plaintext 

reszta postu (za dlugi sie okazal)


po uruchomieniu

[SQL] pobierz, plaintext 
SELECT * FROM get_list_pages_final()
[SQL] pobierz, plaintext 

dostaje takie messages: (bez bledow, bardziej debugowo)

Kod

NOTICE:  NIE TWORZE tt_get_list_pages_temp
NOTICE:  NIE TWORZE tt_get_list_pages_result
NOTICE:  root=(2,"tytul strony")
CONTEXT:  SQL statement "select * from get_list_pages_rek(0)"
PL/pgSQL function "get_list_pages_final" line 28 at execute statement
NOTICE:  depth=1
CONTEXT:  SQL statement "select * from get_list_pages_rek(0)"
PL/pgSQL function "get_list_pages_final" line 28 at execute statement
NOTICE:  root=(3,"inny tytul")
CONTEXT:  PL/pgSQL function "get_list_pages_rek" line 18 at execute statement
SQL statement "select * from get_list_pages_rek(0)"
PL/pgSQL function "get_list_pages_final" line 28 at execute statement
NOTICE:  depth=2
CONTEXT:  PL/pgSQL function "get_list_pages_rek" line 18 at execute statement
SQL statement "select * from get_list_pages_rek(0)"
PL/pgSQL function "get_list_pages_final" line 28 at execute statement
NOTICE:  root=(4,kolejna)
CONTEXT:  PL/pgSQL function "get_list_pages_rek" line 18 at execute statement
PL/pgSQL function "get_list_pages_rek" line 18 at execute statement
SQL statement "select * from get_list_pages_rek(0)"
PL/pgSQL function "get_list_pages_final" line 28 at execute statement
NOTICE:  depth=3
CONTEXT:  PL/pgSQL function "get_list_pages_rek" line 18 at execute statement
PL/pgSQL function "get_list_pages_rek" line 18 at execute statement
SQL statement "select * from get_list_pages_rek(0)"
PL/pgSQL function "get_list_pages_final" line 28 at execute statement
NOTICE:  root=(6,"inny tytul")
CONTEXT:  PL/pgSQL function "get_list_pages_rek" line 18 at execute statement
PL/pgSQL function "get_list_pages_rek" line 18 at execute statement
PL/pgSQL function "get_list_pages_rek" line 18 at execute statement
SQL statement "select * from get_list_pages_rek(0)"
PL/pgSQL function "get_list_pages_final" line 28 at execute statement
NOTICE:  depth=4
CONTEXT:  PL/pgSQL function "get_list_pages_rek" line 18 at execute statement
PL/pgSQL function "get_list_pages_rek" line 18 at execute statement
PL/pgSQL function "get_list_pages_rek" line 18 at execute statement
SQL statement "select * from get_list_pages_rek(0)"
PL/pgSQL function "get_list_pages_final" line 28 at execute statement
NOTICE:  ITERACJA ZAKONCZONA
CONTEXT:  PL/pgSQL function "get_list_pages_rek" line 18 at execute statement
PL/pgSQL function "get_list_pages_rek" line 18 at execute statement
PL/pgSQL function "get_list_pages_rek" line 18 at execute statement
PL/pgSQL function "get_list_pages_rek" line 18 at execute statement
SQL statement "select * from get_list_pages_rek(0)"
PL/pgSQL function "get_list_pages_final" line 28 at execute statement
NOTICE:  ITERACJA ZAKONCZONA
CONTEXT:  PL/pgSQL function "get_list_pages_rek" line 18 at execute statement
PL/pgSQL function "get_list_pages_rek" line 18 at execute statement
PL/pgSQL function "get_list_pages_rek" line 18 at execute statement
SQL statement "select * from get_list_pages_rek(0)"
PL/pgSQL function "get_list_pages_final" line 28 at execute statement
NOTICE:  ITERACJA ZAKONCZONA
CONTEXT:  PL/pgSQL function "get_list_pages_rek" line 18 at execute statement
PL/pgSQL function "get_list_pages_rek" line 18 at execute statement
SQL statement "select * from get_list_pages_rek(0)"
PL/pgSQL function "get_list_pages_final" line 28 at execute statement
NOTICE:  root=(7,"tytul jakistam")
CONTEXT:  PL/pgSQL function "get_list_pages_rek" line 18 at execute statement
SQL statement "select * from get_list_pages_rek(0)"
PL/pgSQL function "get_list_pages_final" line 28 at execute statement
NOTICE:  depth=2
CONTEXT:  PL/pgSQL function "get_list_pages_rek" line 18 at execute statement
SQL statement "select * from get_list_pages_rek(0)"
PL/pgSQL function "get_list_pages_final" line 28 at execute statement
NOTICE:  ITERACJA ZAKONCZONA
CONTEXT:  PL/pgSQL function "get_list_pages_rek" line 18 at execute statement
PL/pgSQL function "get_list_pages_rek" line 18 at execute statement
SQL statement "select * from get_list_pages_rek(0)"
PL/pgSQL function "get_list_pages_final" line 28 at execute statement
NOTICE:  ITERACJA ZAKONCZONA
CONTEXT:  PL/pgSQL function "get_list_pages_rek" line 18 at execute statement
SQL statement "select * from get_list_pages_rek(0)"
PL/pgSQL function "get_list_pages_final" line 28 at execute statement
NOTICE:  root=(9,lalalal)
CONTEXT:  SQL statement "select * from get_list_pages_rek(0)"
PL/pgSQL function "get_list_pages_final" line 28 at execute statement
NOTICE:  depth=2
CONTEXT:  SQL statement "select * from get_list_pages_rek(0)"
PL/pgSQL function "get_list_pages_final" line 28 at execute statement
NOTICE:  root=(10,xcczxczxc)
CONTEXT:  PL/pgSQL function "get_list_pages_rek" line 18 at execute statement
SQL statement "select * from get_list_pages_rek(0)"
PL/pgSQL function "get_list_pages_final" line 28 at execute statement
NOTICE:  depth=3
CONTEXT:  PL/pgSQL function "get_list_pages_rek" line 18 at execute statement
SQL statement "select * from get_list_pages_rek(0)"
PL/pgSQL function "get_list_pages_final" line 28 at execute statement
NOTICE:  ITERACJA ZAKONCZONA
CONTEXT:  PL/pgSQL function "get_list_pages_rek" line 18 at execute statement
PL/pgSQL function "get_list_pages_rek" line 18 at execute statement
SQL statement "select * from get_list_pages_rek(0)"
PL/pgSQL function "get_list_pages_final" line 28 at execute statement
NOTICE:  ITERACJA ZAKONCZONA
CONTEXT:  PL/pgSQL function "get_list_pages_rek" line 18 at execute statement
SQL statement "select * from get_list_pages_rek(0)"
PL/pgSQL function "get_list_pages_final" line 28 at execute statement
NOTICE:  ITERACJA ZAKONCZONA
CONTEXT:  SQL statement "select * from get_list_pages_rek(0)"
PL/pgSQL function "get_list_pages_final" line 28 at execute statement

Total query runtime: 20 ms.
7 rows retrieved.

ogolnie mam nastepujace problemy:

1.

Kod

NOTICE:  root=(2,"tytul strony")
CONTEXT:  SQL statement "select * from get_list_pages_rek(0)"
PL/pgSQL function "get_list_pages_final" line 28 at execute statement
NOTICE:  depth=1

z tego co rozumiem, komunikat o wywolaniu 'get_list_pages_final' pojawia sie przed NOTICE: depth=1 a w kodzie NAJPIERW sie notice o depth a dopiero potem wywolanie - czy moze ty byc problem synchronizacji komunikatow?

2.

zwraca ogolnie dobre wyniki, ale dla page_id = 7 liczy zle, pokazuje 1 a powinno byc dwa - widac w messages ze cos nie tak (rekurencyjne wywoalnia bez wyswietlania NOTICE: root = xxxx ) tam zapewne jest blad ale siedze nad tym juz dlugo i nie potrafie dojsc w ktorym momencie zaczyna sie kaszana (jest to powiazane tez z pkt. 1 mojego pytania)

3.

wiem ze rozwiazanie nie jest optymalne, moze ma ktos lepszy pomysl na rozwiaznie? finalnie funckja ma pmagac rysowac drzewko zaleznosci dla stron w CMS'ie

[Zbłąkany]

ltree