-- -- apm_package_version__sortable_version_name/1 -- create or replace function apm_package_version__sortable_version_name( character varying ) returns varchar as $$ declare version_name alias for $1; a_fields integer; a_start integer; a_end integer; a_order varchar(1000) default ''; a_char char(1); a_seen_letter boolean default 'f'; begin a_fields := 0; a_start := 1; loop a_end := a_start; -- keep incrementing a_end until we run into a non-number while substr(version_name, a_end, 1) >= '0' and substr(version_name, a_end, 1) <= '9' loop a_end := a_end + 1; end loop; if a_end = a_start then return -1; -- raise_application_error(-20000, 'Expected number at position ' || a_start); end if; if a_end - a_start > 4 then return -1; -- raise_application_error(-20000, 'Numbers within versions can only be up to 4 digits long'); end if; -- zero-pad and append the number a_order := a_order || substr('0000', 1, 4 - (a_end - a_start)) || substr(version_name, a_start, a_end - a_start) || '.'; a_fields := a_fields + 1; if a_end > length(version_name) then -- end of string - we're outta here if a_seen_letter = 'f' then -- append the "final" suffix if there haven't been any letters -- so far (i.e., not development/alpha/beta) a_order := a_order || repeat('0000.',7 - a_fields) || ' 3F.'; end if; return a_order; end if; -- what's the next character? if a period, just skip it a_char := substr(version_name, a_end, 1); if a_char = '.' then else -- if the next character was a letter, append the appropriate characters if a_char = 'd' then a_order := a_order || repeat('0000.',7 - a_fields) || ' 0D.'; else if a_char = 'a' then a_order := a_order || repeat('0000.',7 - a_fields) || ' 1A.'; else if a_char = 'b' then a_order := a_order || repeat('0000.',7 - a_fields) || ' 2B.'; end if; end if; end if; -- can't have something like 3.3a1b2 - just one letter allowed! if a_seen_letter = 't' then return -1; -- raise_application_error(-20000, 'Not allowed to have two letters in version name ''' -- || version_name || ''''); end if; a_seen_letter := 't'; -- end of string - we're done! if a_end = length(version_name) then return a_order; end if; end if; a_start := a_end + 1; end loop; end;$$ language plpgsql;