--делаем тип
CREATE TYPE "public"."foo_rec_type" AS (
  "ID" INTEGER,
  "ParentID" INTEGER,
  "Level" integer,
  "FooField" varchar(100)
);
 
--делаем табличку
CREATE TABLE "public"."foo"(
  "ID" INTEGER,
  "ParentID" INTEGER,
  "FooField" varchar(100)
);
 
--ходим от корня к листьям
CREATE OR REPLACE FUNCTION "public"."GetFooWithLevel" (parent integer, curr_level integer) RETURNS SETOF "public"."foo_rec_type" AS
$body$
DECLARE
  lev integer;
  rec foo_rec_type;
BEGIN
 
FOR rec IN SELECT "ID","ParentID",curr_level,"FooField" FROM foo WHERE "Parent" IS NOT DISTINCT FROM parent loop
  RETURN NEXT rec;
  RETURN query SELECT * FROM "GetFooWithLevel"(rec."ID",curr_level+1);
end loop;
 
END;
$body$
LANGUAGE 'plpgsql'
 
--ходим от листа к корню
CREATE OR REPLACE FUNCTION "public"."GetFullFooPath" (foo_id integer) RETURNS text AS
$body$
DECLARE
  parent INTEGER;
  caption text;
BEGIN
 
parent=foo_id;
SELECT "ParentID","FooField" INTO parent,caption  FROM foo WHERE "ID"=foo_id;
IF parent=0 THEN	
  RETURN caption;
else
  RETURN  "GetFullFooPath"(parent)|| E'\\' ||caption ;
end IF;
 
END;
$body$
LANGUAGE 'plpgsql'
 
--добавляем данные
INSERT INTO foo("ID","ParentID","FooField") VALUES(1,0,'c:');
INSERT INTO foo("ID","ParentID","FooField") VALUES(2,0,'d:');
INSERT INTO foo("ID","ParentID","FooField") VALUES(3,1,'windows');
INSERT INTO foo("ID","ParentID","FooField") VALUES(4,1,'program files');
INSERT INTO foo("ID","ParentID","FooField") VALUES(5,2,'data');
INSERT INTO foo("ID","ParentID","FooField") VALUES(6,3,'system32');
INSERT INTO foo("ID","ParentID","FooField") VALUES(7,4,'PostgreSQL');
INSERT INTO foo("ID","ParentID","FooField") VALUES(8,5,'backup');
 
--тестируем... 
SELECT "GetFullFooPath"("ID") FROM "GetFooWithLevel"(0,1) 
WHERE "Level"=(SELECT max("Level") FROM "GetFooWithLevel"(0,1) )
 
--Ура! <img class="ccfilter smileys" src="https://pgdocs.ru/sites/all/modules/ccfilter/smileys/ab.gif" alt=":)" title=":)" />
d:\DATA\backup
c:\windows\system32
c:\program files\PostgreSQL