From f30cdfc0dd86119e0efcd318d08589fd52be4ab4 Mon Sep 17 00:00:00 2001 From: "Steinar H. Gunderson" Date: Sat, 11 Jun 2005 23:18:45 +0000 Subject: [PATCH] Make a (somewhat inefficient?) stored procedure implementation of finding min and max score for players. --- sql/ccbs.sql | 50 ++++++++++++++++++++++++++++++++++++++++++++++++++ 1 file changed, 50 insertions(+) diff --git a/sql/ccbs.sql b/sql/ccbs.sql index d48fbeb..fa63774 100644 --- a/sql/ccbs.sql +++ b/sql/ccbs.sql @@ -256,3 +256,53 @@ $$ STABLE RETURNS NULL ON NULL INPUT; +-- Find out, for each player in the group, what the maximum remaining score +-- possibly can be, using get_max_score_for_{songs,players} (the first for +-- selected but not played songs, the second for random songs that have not +-- been chosen yet). +-- +-- This doesn't solve problems we'd face with more than one chosen song, but it +-- should be good enough. + +CREATE TYPE minmax_score AS ( + player INTEGER, + min_score INTEGER, + max_score INTEGER +); + +-- get_minmax_score_for_players(tournament, round, playmode) +CREATE FUNCTION get_minmax_score_for_players(integer, integer, varchar) RETURNS SETOF minmax_score + AS $$ +DECLARE + ret minmax_score; + tp RECORD; + ts RECORD; +BEGIN + FOR tp IN SELECT player FROM roundparticipation WHERE tournament=$1 AND round=$2 LOOP + ret.player := tp.player; + ret.min_score := 0; + ret.max_score := 0; + + FOR ts IN SELECT song,score FROM scores WHERE tournament=$1 and round=$2 and player=tp.player LOOP + IF ts.score IS NOT NULL THEN + -- score is already given + ret.min_score := ret.min_score + ts.score; + ret.max_score := ret.max_score + ts.score; + ELSE + IF ts.song IS NOT NULL THEN + ret.max_score := ret.max_score + ( SELECT max_score FROM get_max_score_for_songs($1, $3) WHERE song=ts.song ); + ELSE + ret.max_score := ret.max_score + ( SELECT max_score FROM get_max_score_for_players($1, $3) WHERE song=tp.player ); + END IF; + END IF; + END LOOP; + + RETURN NEXT ret; + END LOOP; + RETURN; +END; +$$ + LANGUAGE plpgsql + STABLE + RETURNS NULL ON NULL INPUT; + -- 2.39.2