-- -- content_keyword__get_path/1 -- create or replace function content_keyword__get_path( integer ) returns text as $$ declare get_path__keyword_id alias for $1; v_path text default ''; v_is_found boolean default 'f'; v_heading cr_keywords.heading%TYPE; v_rec record; begin -- select -- heading -- from ( -- select -- heading, level as tree_level -- from cr_keywords -- connect by prior parent_id = keyword_id -- start with keyword_id = get_path.keyword_id) k -- order by -- tree_level desc for v_rec in select heading from (select k2.heading, tree_level(k2.tree_sortkey) as tree_level from cr_keywords k1, cr_keywords k2 where k1.keyword_id = get_path__keyword_id and k1.tree_sortkey between k2.tree_sortkey and tree_right(k2.tree_sortkey)) k order by tree_level desc LOOP v_heading := v_rec.heading; v_is_found := 't'; v_path := v_path || '/' || v_heading; end LOOP; if v_is_found = 'f' then return null; else return v_path; end if; end;$$ language plpgsql;