Move FetchMaxScoreForSongs into a stored procedure.

[ccbs] / sql / ccbs.sql

diff --git a/sql/ccbs.sql b/sql/ccbs.sql
index 756f54c4e5c35f09eb092e7b29ee7908a396e303..4c42d56187a4c01f185ead62367b10f081503ff5 100644 (file)
--- a/sql/ccbs.sql
+++ b/sql/ccbs.sql
@@ -200,3 +200,27 @@ CREATE TABLE bigscreen.active_groups (
 CREATE TABLE bigscreen.active_screens (
        id VARCHAR NOT NULL PRIMARY KEY
 );
+
+-- get_max_score_for_songs(tournament, playmode)
+CREATE TYPE max_score AS (
+       song INTEGER,
+       max_score INTEGER
+);
+
+CREATE FUNCTION get_max_score_for_songs(integer, varchar) RETURNS SETOF max_score
+    AS $$
+       SELECT
+           song,
+           MAX(feetrating)*1000 AS max_score
+       FROM songratings
+       WHERE
+           machine=(
+               SELECT machine FROM tournaments WHERE tournament=$1
+            )
+           AND playmode=$2
+       GROUP BY song
+       ;
+$$
+    LANGUAGE SQL
+    STABLE
+    RETURNS NULL ON NULL INPUT;