Drukowana wersja tematu

Napisany przez: phpion 24.07.2008, 08:42:12

Witam,
mam struktruę tabeli:
p_category: id, parent_id, name
czyli w zasadzie standardzik. Chciałbym napisać funkcję, która zwróci mi ścieżkę od danej kategorii do korzenia. Mam coś takiego:

[SQL] pobierz, plaintext 
CREATE OR REPLACE FUNCTION f_get_category_path(category_id integer, shop_id integer)
  RETURNS p_category AS $BODY$DECLARE
	r p_category%ROWTYPE;
BEGIN SELECT * INTO r FROM p_category WHERE id=$1;
 
	IF (r.parent_id > 0) THEN
		-- SELECT * FROM f_get_category_path(r.parent_id, $2);
	END IF;
 
	RETURN r;
END;$BODY$
  LANGUAGE 'plpgsql' VOLATILE
  COST 100;
[SQL] pobierz, plaintext 

Mam problem z rekurencyjnym wywołaniem tej funkcji. Jeśli odkomentuję linijkę w warunku otrzymuję:

Jak to poprawnie zapisać?

pion

Napisany przez: Sedziwoj 24.07.2008, 12:59:40

Jak dobrze pamiętam, to PG nie lubi jak mus się wstawia SQL z parametrami... spróbuj coś w tym stylu:

[SQL] pobierz, plaintext 
EXECUTE 'SELECT * FROM f_get_category_path('||r.parent_id||', '||$2||');'
[SQL] pobierz, plaintext 

Ogólnie o PREPARE:
http://www.postgresql.org/docs/current/static/sql-prepare.html

Do tego moim zadaniem powinieneś użyć LOOP, będzie to moim zdaniem lepsze rozwiązanie, skoro jedynie będzie jedna gałąź rekurencji do tego z jednym elementem do zapamiętania.
Trzeba zakładać, że nie pojawi się błąd pętli, bo to od razu zabije bazę (nie zależnie czy algorytm będzie rekurencyjny czy iteracyjny).

Napisany przez: phpion 24.07.2008, 13:58:26

Dzięki za odpowiedź. Teraz ciało funkcji wygląda w ten sposób:

[SQL] pobierz, plaintext 
DECLARE
	r p_category%ROWTYPE;
BEGIN SELECT * INTO r FROM p_category WHERE id=$1;
 
	IF (r.parent_id IS NOT NULL AND r.parent_id > 0) THEN
		EXECUTE 'SELECT * FROM f_get_category_path('||r.parent_id||', '||$2||');';
	END IF;
 
	RETURN r;
END;
[SQL] pobierz, plaintext 

Wywołanie np.

[SQL] pobierz, plaintext 
SELECT * FROM f_get_category_path(7, 1);
[SQL] pobierz, plaintext 

powinno zwrócić (wg moich zamierzeń) 3 rekordy, natomiast zwraca tylko jeden (ten o id=7 czyli parametrze przekazanym w funkcji). Wygląda jakby albo nie wchodził w ogóle do warunku (co jest mało prawdopodobne bo na pewno jest spełniony) albo nie wyświetlał rezultatów kolejny wywołań. Jeśli mógłbyś mi pomóc w tej kwestii byłbym bardzo wdzięczny.

Napisany przez: Sedziwoj 24.07.2008, 15:18:27

Może nie za piękne... Ważne w deklaracji "SETOF "

[SQL] pobierz, plaintext 
CREATE OR REPLACE FUNCTION f_get_category_path(category_id integer, shop_id integer)
  RETURNS SETOF p_category AS $BODY$
DECLARE
	r p_category%ROWTYPE;
	aid integer;
BEGIN IF category_id IS NULL THEN RETURN; END IF;
	aid := category_id;
LOOP
	FOR r IN SELECT * FROM p_category WHERE id = aid
	LOOP
		RETURN NEXT r;
	END LOOP;
	EXIT WHEN NOT r.parent_id > 0;  -- Nie wiem czy masz null, czy może błędnie 0 w tym polu jak nie ma rodzica.
	aid := r.parent_id;
END LOOP;
RETURN;
END;
$BODY$
  LANGUAGE 'plpgsql' VOLATILE;
[SQL] pobierz, plaintext 

EDIT: parę poprawek
EDIT2: poprawiam to co napisał phpion, aby był poprawny przykład, jakby ktoś sszukał

Napisany przez: phpion 24.07.2008, 16:30:16

Działa wyśmienicie. Musiałem poprawić tylko jedną linijkę:

[SQL] pobierz, plaintext 
FOR r IN SELECT * FROM p_category WHERE id = aid
[SQL] pobierz, plaintext 

bo wywalało błędy. Naprawdę ogromne dzięki!

PS: tworzę "abstrakcyjną" kategorię ROOT o id=0 i parent_id=NULL, natomiast później wszystkie kategorie główne mają ustawione parent_id=0

Napisany przez: Sedziwoj 24.07.2008, 17:02:22

Ja zazwyczaj miałem las, Ty chcesz mieć drzewo, to już nie nazwę błędem Po prostu inna koncepcja (a mi się przypomniało jak było w bazie -1 jak nie było powiązania... i tylko dla tego wspomniałem)
Ciągle mam wrażenie że da się to lepiej napisać, ale bym musiał się tym pobawić, do tej pory nie miałem potrzeby robienia takich funkcji.

Napisany przez: phpion 15.12.2008, 11:58:40

Odświeżam temat: mam problem z modyfikacją wyżej podanego rozwiązania. Aktualnie mam tabele:

category: id, parent_id, position
locale: id, code
category_i18n: category_id, locale_id, name

i próbuję pobrać ścieżkę kategorii czyli np. Podkategoria -> Kategoria. Stworzyłem widok:

[SQL] pobierz, plaintext 
CREATE OR REPLACE VIEW shp_view_category AS SELECT a.category_id, b.parent_id, a.name, c.code AS locale_code
   FROM shp_category_i18n a
   LEFT JOIN shp_category b ON b.id = a.category_id
   LEFT JOIN shp_locale c ON c.id = a.locale_id;
[SQL] pobierz, plaintext 

oraz zmodyfikowałem funkcję tak by (teoretycznie) działała poprawnie:

[SQL] pobierz, plaintext 
CREATE OR REPLACE FUNCTION f_get_category_path(category_id integer, locale character varying)
  RETURNS SETOF shp_view_category AS $BODY$DECLARE
	r shp_view_category%ROWTYPE;
	aid integer;
BEGIN IF $2 IS NULL THEN
		RETURN;
	END IF;
 
	aid := $2;
 
	LOOP
		FOR r IN SELECT * FROM shp_view_category WHERE category_id=aid AND locale_code=$2
		LOOP
			RETURN NEXT r;
		END LOOP;
 
		EXIT WHEN r.parent_id=0 OR r.parent_id IS NULL;
 
		aid := r.parent_id;
	END LOOP;
 
	RETURN;
END;$BODY$
  LANGUAGE 'plpgsql' VOLATILE
  COST 100
  ROWS 1000;
[SQL] pobierz, plaintext 

Wywłouję ją:

[SQL] pobierz, plaintext 
SELECT * FROM f_get_category_path(12, 'pl_PL');
[SQL] pobierz, plaintext 

Nie działa ona jednak poprawnie. W wyniku otrzymuję wszystkie rekordy z widoku, które posiadają tłumaczenia dla kategorii 12 w wersji 'pl_PL', a nie tylko te wchodzące w skład ścieżki. Co robię źle :|

Proszę o pomoc,
pion

PS: wiem, że lepiej to zrobić z użyciem RECORD a nie tworzyć specjalnie po to widok ale na razie chciałbym się uporać z tym aby to po prostu zaczęło działać...