1 CREATE VIEW current_planet_stats AS
2 SELECT p.pid, p.nick, p.planet_status, p.hit_us, ps.x, ps.y, ps.z, p.ruler, p.planet, p.race
3 ,alliance, a.relationship, a.aid, p.channel, p.ftid, p.gov
4 ,ps.size, ps.score, ps.value, ps.xp, ps.sizerank, ps.scorerank, ps.valuerank, ps.xprank
5 FROM ( SELECT pid, tick, x, y, z, size, score, value, xp, sizerank, scorerank, valuerank, xprank
7 WHERE tick = ( SELECT max(tick) AS max FROM planet_stats)
10 LEFT JOIN alliances a USING (alliance);
12 CREATE OR REPLACE VIEW current_planet_stats_full AS
18 WHERE tick = ( SELECT max(tick) AS max FROM planet_stats)
20 LEFT JOIN alliances USING (alliance);
22 CREATE OR REPLACE FUNCTION change_member() RETURNS trigger
25 IF TG_OP = 'INSERT' THEN
27 UPDATE planets SET alliance = 'NewDawn' WHERE
28 pid = (SELECT pid FROM users WHERE uid = NEW.uid);
30 ELSIF TG_OP = 'DELETE' THEN
32 UPDATE planets SET alliance = NULL WHERE
33 pid = (SELECT pid FROM users WHERE uid = OLD.uid);
39 $_X$ LANGUAGE plpgsql;