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