--
-- bm_bookmarks_get_folder_names/2
--
create or replace function bm_bookmarks_get_folder_names(
  integer,
  text
) returns text as $$

declare
	p_bookmark_id	alias for $1;
	p_sep		alias for $2;	-- optional separator to use
	v_rec		record;
	tree_key	varbit;
	separator	text default ' :: ';
	folder_names	text default '';	-- If NULL, the || in the LOOP fails
begin
	tree_key := bm_bookmarks_get_tree_sortkey(p_bookmark_id);
	
	-- Level 1 is the root folder, level 2 is items in the root folder
	if tree_level(tree_key) <= 2 then
		return '';
	end if;
	
	if p_sep is not null then
		separator := p_sep;
	end if;

	for v_rec in select local_title
		from bm_bookmarks
		where tree_sortkey in
			(select tree_ancestor_keys( -- get all parent folders up to level 2
					tree_ancestor_key(	-- start with the parent folder key
						tree_key, tree_level(tree_key) - 1), 2 ) )
		order by tree_sortkey
	LOOP
		folder_names := folder_names || separator || v_rec.local_title;
	end LOOP;

	return trim(leading separator from folder_names);
end;$$ language plpgsql;