--
-- 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;