34 lines
792 B
MySQL
34 lines
792 B
MySQL
|
|
||
|
|
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
|