Change database structure for planets and alliances + PG 8.4 changes

[ndwebbie.git] / database / noperl.sql

diff --git a/database/noperl.sql b/database/noperl.sql
index f8821343a14e18ecdb26a00951577fb12a09f6a2..48fcbe30ba80cbf9d0de652f788169ae9ee510b9 100644 (file)
--- a/database/noperl.sql
+++ b/database/noperl.sql
@@ -2,47 +2,48 @@ CREATE OR REPLACE FUNCTION find_planet_id(_ruler text, _planet text, _race race)
     AS $_$
 DECLARE
        p RECORD;
-       pid INTEGER;
+       id INTEGER;
        thread INTEGER;
 BEGIN
-       SELECT id, race INTO p FROM planets WHERE ruler = _ruler AND planet = _planet;
+       SELECT pid, race INTO p FROM planets WHERE ruler = _ruler AND planet = _planet;
        IF FOUND THEN
                IF _race <> p.race THEN
-                       UPDATE planets SET race = _race WHERE id = p.id;
+                       UPDATE planets SET race = _race WHERE pid = p.pid;
                END IF;
-               pid := p.id;
+               id := p.pid;
        ELSE
                INSERT INTO forum_threads (fbid,subject,uid) VALUES(-2, _ruler || ' OF ' || _planet, -3)
                        RETURNING ftid INTO thread;
                INSERT INTO planets(ruler,planet,race,ftid) VALUES(_ruler,_planet,_race,thread)
-                       RETURNING id INTO pid;
+                       RETURNING pid INTO id;
        END IF;
-       RETURN pid;
+       RETURN id;
 END;
 $_$
     LANGUAGE plpgsql;
 
-DROP FUNCTION findplanetid(character varying, character varying, character varying);
+DROP FUNCTION IF EXISTS findplanetid(character varying, character varying, character varying);
 
-CREATE OR REPLACE FUNCTION find_alliance_id(alliance text) RETURNS integer
+CREATE OR REPLACE FUNCTION find_alliance_id(alli text) RETURNS integer
     AS $_$
 DECLARE
-       aid INTEGER;
+       id INTEGER;
 BEGIN
-       SELECT id FROM INTO aid alliances WHERE name = alliance;
+       SELECT aid FROM INTO id alliances WHERE alliance = alli;
        IF NOT FOUND THEN
-               INSERT INTO alliances(name) VALUES($1)
-                       RETURNING id INTO aid;
+               INSERT INTO alliances(alliance) VALUES($1)
+                       RETURNING aid INTO id;
        END IF;
-       RETURN aid;
+       RETURN id;
 END;
 $_$
     LANGUAGE plpgsql;
 
-DROP FUNCTION find_alliance_id(character varying);
+DROP FUNCTION IF EXISTS find_alliance_id(character varying);
 
 CREATE OR REPLACE FUNCTION coords(x integer, y integer, z integer) RETURNS text
     AS $_$
 SELECT $1 || ':' || $2 || ':' || $3
 $_$
     LANGUAGE sql IMMUTABLE;
+