--
-- etp__get_relative_url/2
--
create or replace function etp__get_relative_url(
  integer,
  character varying
) returns varchar as $$

declare
  p_item_id alias for $1;
  p_name alias for $2;
  v_item_id integer;
  v_url varchar;
  v_object_type varchar;
  v_link_rec record;
begin

  select object_type into v_object_type
    from acs_objects
   where object_id = p_item_id;

  if v_object_type = 'content_item' then
    return p_name;
  end if;

  if v_object_type = 'content_folder' then
    select s.name || '/' into v_url
    from cr_folders f, site_nodes s
    where f.folder_id = p_item_id
    and s.object_id = f.package_id;
    return v_url;
  end if;

  if v_object_type = 'content_extlink' then
    select url into v_url
      from cr_extlinks
     where extlink_id = p_item_id;
    return v_url;
  end if;

  if v_object_type = 'content_symlink' then
    select target_id into v_item_id
      from cr_symlinks
     where symlink_id = p_item_id;

    select f.package_id, i.name
      into v_link_rec
      from cr_items i, cr_folders f
     where i.item_id = v_item_id
       and i.parent_id = f.folder_id;

    select site_node__url(s.node_id) into v_url
      from site_nodes s
     where s.object_id = v_link_rec.package_id;

    return v_url || v_link_rec.name;

  end if;

  return null;

end;
$$ language plpgsql;