
CREATE OR REPLACE FUNCTION count_matching_tags (CHARACTER VARYING, CHARACTER VARYING[])
RETURNS INTEGER AS $$
    DECLARE
        matches INTEGER := 0;
        tag CHARACTER VARYING;
    BEGIN
        FOREACH tag IN ARRAY $2 LOOP
            IF POSITION(tag IN $1) > 0 THEN
                matches = matches + 1;
            END IF;
        END LOOP;
        RETURN matches;
    END;
$$
LANGUAGE PLPGSQL


CREATE OR REPLACE FUNCTION get_matching_league (CHARACTER VARYING, INTEGER)
RETURNS INTEGER AS $$
    DECLARE
        lid INTEGER;
    BEGIN
        SELECT id
        INTO lid
        FROM mainapp_league
        WHERE sport_id = $2 AND tags IS NOT NULL AND count_matching_tags($1, tags) > 0
        ORDER BY degree ASC
        LIMIT 1;
        RETURN lid;
    END;
$$
LANGUAGE PLPGSQL
