clear markdown compare help donate or buy diddy dollings comments/suggestions/bugs a leap of faith? bible365
clear markdown donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 582681 fiddles created (13280 in the last week).

CREATE TYPE game_tag AS( tag_name text, tag_value text ); CREATE TABLE games( game_id serial PRIMARY KEY -- .. more columns ); INSERT INTO games(game_id) VALUES (6), (38); CREATE TABLE tags ( tag_id serial PRIMARY KEY, game_id int, -- REFERENCES games(game_id), tag_name text, tag_value text ); INSERT INTO tags (tag_id, game_id, tag_name, tag_value) VALUES ( 55, 6, 'Event', 'EUR-ASIA Rapid Match'), ( 58, 6, 'Round', '5'), (400, 38, 'Event', 'EUR-ASIA Rapid Match'), (403, 38, 'Round', '4'); CREATE INDEX ON tags (tag_name, tag_value, game_id);
2 rows affected
4 rows affected
 hidden batch(es)


CREATE OR REPLACE FUNCTION f_games_by_tags(VARIADIC _filters game_tag[]) RETURNS table (game_id int) AS $func$ BEGIN CASE cardinality(_filters) -- WHEN 0 THEN -- impossible WHEN 1 THEN RETURN QUERY SELECT t.game_id FROM tags t WHERE (tag_name, tag_value) = _filters[1]; WHEN 2 THEN -- optional RETURN QUERY SELECT t.game_id FROM tags t WHERE (tag_name, tag_value) = _filters[1] AND EXISTS ( SELECT FROM tags t1 WHERE t1.game_id = t.game_id AND (tag_name, tag_value) = _filters[2] ); ELSE RETURN QUERY EXECUTE (SELECT 'SELECT game_id FROM tags t WHERE (tag_name, tag_value) = $1[1] AND ' || string_agg('EXISTS (SELECT FROM tags WHERE game_id = t.game_id AND (tag_name, tag_value) = $1[' || g || '])', ' AND ') FROM generate_series (2, cardinality(_filters)) g) USING _filters; END CASE; END $func$ LANGUAGE plpgsql;
 hidden batch(es)


SELECT * FROM f_games_by_tags('(Event,"EUR-ASIA Rapid Match")')
game_id
6
38
 hidden batch(es)


SELECT * FROM f_games_by_tags('(Round,5)', '(Event,"EUR-ASIA Rapid Match")')
game_id
6
 hidden batch(es)


SELECT * FROM f_games_by_tags('(Round,5)', '(Event,"EUR-ASIA Rapid Match")', '(Event,"EUR-ASIA Rapid Match")', '(Event,"EUR-ASIA Rapid Match")')
game_id
6
 hidden batch(es)