Ticket #15652: public.string.sql

/**
 * @param varchar _str The input string.
 * @return varchar The unaccented string.
 */
CREATE OR REPLACE FUNCTION public.unaccent(varchar)
        RETURNS varchar
AS $$
DECLARE
        _str    varchar := $1;
        _find   constant varchar :=     'ÀÁÂÃÄÅĀĄĂÆÈÉÊËĒĘĚĔĖÐÌÍÎÏĪĨĬĮİIJÒÓÔÕÖØŌŐŎŒÙÚÛÜŪŮŰŬŨŲÝŶŸ' ||
                                                                'àáâãäåāąăæèéêëēęěĕėðìíîïīĩĭįıijòóôõöøōőŏœùúûüūůűŭũųýÿŷ' ||
                                                                'ÇĆČĈĊĎĐƑĜĞĠĢĤĦĴĶŁĽĹĻĿÑŃŇŅŊŔŘŖŚŠŞŜȘſŤŢŦȚÞŴŹŽŻ' ||
                                                                'çćčĉċďđƒĝğġģĥħĵĸķłľĺļŀñńňņʼnŋŕřŗśšşŝșßťţŧțþŵžżź';
        _repl   constant varchar :=     'AAAAAAAAAAEEEEEEEEEEIIIIIIIIIIOOOOOOOOOOUUUUUUUUUUYYY' ||
                                                                'aaaaaaaaaaeeeeeeeeeeiiiiiiiiiioooooooooouuuuuuuuuuyyy' ||
                                                                'CCCCCDDFGGGGHHJKLLLLLNNNNNRRRSSSSSSTTTTTWZZZ' ||
                                                                'cccccddfgggghhjkklllllnnnnnnrrrsssssstttttwzzz';
BEGIN
        -- Bail if the string is empty
        IF      _str = ''
        THEN
                RETURN _str;
        END IF;
        
        -- Common expansions
        _str := replace(_str, 'ſ', 'SS');
        _str := replace(_str, 'ß', 'ss');
        _str := replace(_str, 'Å', 'AA');
        _str := replace(_str, 'å', 'aa');
        _str := replace(_str, 'IJ', 'IJ');
        _str := replace(_str, 'ij', 'ij');
        _str := regexp_replace(_str, '([ÄÆÖŒÜ])', E'\\1E', 'g');
        _str := regexp_replace(_str, '([äæöü])', E'\\1e', 'g');
        
        -- Translate what's left
        _str := translate(_str, _find, _repl);
        
        RETURN _str;
END;
$$ IMMUTABLE STRICT LANGUAGE plpgsql COST 1;

COMMENT ON FUNCTION public.unaccent(varchar) IS
'Strips accents from a string. Useful if the built-in unaccent() won''t
compile properly, as is the case for the one from macports...';

/**
 * @param varchar _str The input string.
 * @param char _sep The separator.
 * @return varchar The output slug.
 */
CREATE OR REPLACE FUNCTION public.to_slug(varchar, varchar = '-')
        RETURNS varchar
AS $$
DECLARE
        _str            varchar := $1;
        _sep            constant varchar(1) := $2;
BEGIN
        -- Bail if the string is empty
        IF      _str = ''
        THEN
                RETURN _str;
        END IF;
        
        -- Strip accents and lower the case
        _str := lower(public.unaccent(_str));
        
        -- Replace nonsensical characters
        _str := regexp_replace(_str, '[^a-z0-9]+', _sep, 'g');
        
        -- Trim the result
        _str := trim(both _sep from _str);
        
        RETURN _str;
END;
$$ IMMUTABLE STRICT LANGUAGE plpgsql COST 1;

COMMENT ON FUNCTION public.to_slug(varchar, varchar) IS
'"Slugifies" a string, e.g. turns A1@bC! into a1-bc for use as slugs.';

/**
 * @param varchar The input string.
 * @return varchar The output string for consumption in natural sorting.
 */
CREATE OR REPLACE FUNCTION public.natsort(varchar)
        RETURNS varchar
AS $$
DECLARE
        _str    varchar := $1;
        _pad    int             := 15; -- Maximum precision for PostgreSQL floats
BEGIN
        -- Bail if the string is empty
        IF      trim(_str) = ''
        THEN
                RETURN '';
        END IF;
        
        -- Strip accents and lower the case
        _str := lower(public.unaccent(_str));
        
        -- Replace nonsensical characters
        _str := regexp_replace(_str, E'[^a-z0-9$¢£¥₤€@&%\\(\\)\\[\\]\\{\\}_:;,\\.\\?!\\+\\-]+', ' ', 'g');
        
        -- Trim the result
        _str := trim(_str);
        
        -- We're done if the string contains no numbers
        IF      _str !~ '[0-9]'
        THEN
                RETURN _str;
        END IF;
        
        -- Force spaces between numbers, so we can use regexp_split_to_table()
        _str := regexp_replace(_str, E'((?:[0-9]+|[0-9]*\\.[0-9]+)(?:e[+-]?[0-9]+\\M)?)', E' \\1 ', 'g');
        
        -- Pad zeros to obtain a reasonably natural looking sort order
        RETURN array_to_string(ARRAY (
        SELECT  CASE
                        WHEN val !~ E'^\\.?[0-9]' THEN
                                -- Not a number; return as is
                                val
                        ELSE
                                -- Do our best...
                                COALESCE(lpad(substring(val::numeric::text from '^[0-9]+'), _pad, '0'), '') ||
                                COALESCE(rpad(substring(val::numeric::text from E'\\.[0-9]+'), _pad, '0'), '')
                        END
        FROM    regexp_split_to_table(_str, E'\\s+') as val
        WHERE   val <> ''
        ), ' ');
END;
$$ IMMUTABLE STRICT LANGUAGE plpgsql COST 1;

COMMENT ON FUNCTION public.natsort(varchar) IS
'Rewrites a string so it can be used in natural sorting.

It''s by no means bullet proof, but it works for reasonably small positive
numbers, and it''s fast enough to be used in a trigger or an index.';