PostgreSQL glob matching

glob_match.sql

CREATE OR REPLACE FUNCTION glob_match(text, text) RETURNS boolean AS $$
DECLARE
    input_string text := $1;
    glob_pattern text := $2;
    double_star_pos integer;
    prefix text;
    suffix text;
    prefix_regex text;
    suffix_regex text;
    path_regex text;
BEGIN
   -- Note - this does not handle patterns with more than one double-star!!! ---

    -- Handle case without double star
    IF glob_pattern NOT LIKE '%**%' THEN
        -- Convert glob to regex, escaping special characters
        path_regex := regexp_replace(glob_pattern, '([.^$+(){}[\]|\\])', '\\\1', 'g');
        
        -- Replace * with regex for any chars except /
        path_regex := replace(path_regex, '*', '[^/]*');
        
        -- Replace ? with regex for any single char except /
        path_regex := replace(path_regex, '?', '[^/]');
        
        -- Add anchors for full string match
        path_regex := '^' || path_regex || '$';
        
        -- Return the match result
        RETURN input_string ~ path_regex;
    END IF;
    
    -- Find the position of the double star
    double_star_pos := position('**' in glob_pattern);
    
    -- Split into prefix and suffix
    prefix := substring(glob_pattern from 1 for double_star_pos - 1);
    suffix := substring(glob_pattern from double_star_pos + 2);
    
    -- Convert prefix and suffix to regex patterns
    IF prefix = '' THEN
        prefix_regex := '';
    ELSE
        prefix_regex := regexp_replace(prefix, '([.^$+(){}[\]|\\])', '\\\1', 'g');
        prefix_regex := replace(prefix_regex, '*', '[^/]*');
        prefix_regex := replace(prefix_regex, '?', '[^/]');
    END IF;
    
    IF suffix = '' THEN
        suffix_regex := '';
    ELSE
        suffix_regex := regexp_replace(suffix, '([.^$+(){}[\]|\\])', '\\\1', 'g');
        suffix_regex := replace(suffix_regex, '*', '[^/]*');
        suffix_regex := replace(suffix_regex, '?', '[^/]');
    END IF;
    
    -- Case 1: Pattern starts with ** (prefix is empty)
    IF prefix = '' THEN
        -- If suffix starts with a separator, remove it for regex
        IF left(suffix, 1) = '/' THEN
            suffix_regex := substring(suffix_regex from 2);
        END IF;
        
        -- With empty prefix, either match the entire string or just check suffix
        IF suffix = '' THEN
            RETURN TRUE; -- ** matches everything
        ELSE
            -- For **suffix patterns, allow suffix to match at the end with optional directories
            RETURN input_string ~ ('(?:.*?)' || suffix_regex || '$');
        END IF;
    END IF;
    
    -- Case 2: Pattern ends with ** (suffix is empty)
    IF suffix = '' THEN
        RETURN input_string ~ ('^' || prefix_regex);
    END IF;
    
    -- Case 3: ** is in the middle of the pattern
    -- Handle the case where there's no / after ** (e.g., a/**b)
    IF left(suffix, 1) <> '/' THEN
        -- Need to match prefix at start and suffix at end, with anything in between
        RETURN input_string ~ ('^' || prefix_regex || '.*' || suffix_regex || '$');
    ELSE
        -- Regular case: prefix/**/suffix
        -- Remove the leading / from suffix_regex for matching
        suffix_regex := substring(suffix_regex from 2);
        -- Match prefix at start and suffix at end with optional directories
        RETURN input_string ~ ('^' || prefix_regex || '(?:.*?/)?' || suffix_regex || '$');
    END IF;
END;
$$ LANGUAGE plpgsql IMMUTABLE;