{
score->erase(score->begin(), score->end());
- pqxx::result res( t.exec("SELECT song,MAX(feetrating)*1000 AS max_score FROM songratings WHERE "
- " machine=( SELECT machine FROM tournaments WHERE tournament=" + pqxx::to_string(tournament) + ") AND playmode='single' GROUP BY song") );
-
+ pqxx::result res( t.exec("SELECT get_max_score_for_songs("
+ + pqxx::to_string(tournament) + ", 'single')") );
+
for (pqxx::result::const_iterator i = res.begin(); i != res.end(); ++i) {
unsigned song, max_score;
song = i["song"].as(song);
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;