1 DROP VIEW available_ships;
2 CREATE VIEW available_ships AS
3 SELECT uid,username, planet, ship
4 , COALESCE(f.amount - o.amount,f.amount) AS amount
5 , COALESCE(fleets,3) AS fleets
7 SELECT planet,ship,amount
9 SELECT DISTINCT ON (planet,mission) planet,mission, fid
12 AND name IN ('Main','Advanced Unit')
13 AND mission = 'Full fleet'
14 ORDER BY planet,mission,tick DESC, fid DESC
16 JOIN fleet_ships fs USING (fid)
18 ) f USING (planet) LEFT OUTER JOIN (
19 SELECT planet, ship, SUM(fs.amount) AS amount, 3 - COUNT(DISTINCT fid) AS fleets
21 JOIN launch_confirmations USING (fid)
22 JOIN fleet_ships fs USING (fid)
24 AND landing_tick - eta - 12 < tick()
26 ) o USING (planet,ship)
27 WHERE COALESCE(f.amount - o.amount,f.amount) > 0