From 341ec6fa54b9ceee749bd15a12774a8ac74f6945 Mon Sep 17 00:00:00 2001 From: =?utf8?q?Bj=C3=B8rn=20Rustad?= Date: Thu, 15 Aug 2013 22:10:20 +0200 Subject: [PATCH] Get points in stage_team(int, int) --- duplo.sql | 10 +++++++--- 1 file changed, 7 insertions(+), 3 deletions(-) diff --git a/duplo.sql b/duplo.sql index 3e9f3d7..99828b0 100644 --- a/duplo.sql +++ b/duplo.sql @@ -195,14 +195,14 @@ DECLARE BEGIN num = 0; FOR stage IN SELECT DISTINCT stage, "date" FROM rider_manager_team NATURAL JOIN stage WHERE team = $1 ORDER BY "date" LOOP - EXECUTE + EXECUTE SELECT * FROM stage; END LOOP; RETURN num; END; $$ LANGUAGE plpgsql; -CREATE OR REPLACE FUNCTION stage_team (int, int) RETURNS TABLE (rider int, name varchar, stage int, team int, team_name varchar, price int, type rider_type) +CREATE OR REPLACE FUNCTION stage_team (int, int) RETURNS TABLE (rider int, name varchar, stage int, team int, team_name varchar, price int, type rider_type, points integer) AS $$ SELECT @@ -212,7 +212,8 @@ SELECT t.team, t.name AS team_name, rr.price, - rr.type + rr."type", + rs.points FROM rider_manager_team AS rmt JOIN @@ -227,6 +228,9 @@ ON (r.rider = rr.rider) JOIN team AS t ON (rr.team = t.team) +LEFT OUTER JOIN + rider_stage AS rs +ON (r.rider = rs.rider AND $1 = rs.stage) WHERE rmt.manager_team = $2 AND -- 2.47.3