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

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/sta...ql-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).

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.

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ł

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

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.

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ć...