--Mike Mucci --5/18/14 --An item can be added to offensive/defensive/general item table. --To be added to the Offensive and Defensive table, additional parameters must be passed in -- |optional-> --addItem(name:TEXT,notes:TEXT,[offensive:ARRAY[2]or NULL,defensive:ARRAY[4]or NULL,weight:REAL,tailCut:BOOLEAN,additionalNotes:TEXT]) CREATE OR REPLACE FUNCTION addItem ( a_name TEXT, a_notes TEXT DEFAULT '', --required off_arr INTEGER[] DEFAULT NULL, --optional def_arr INTEGER[] DEFAULT NULL, --optional a_weight REAL DEFAULT NULL, --needed if any array is passed in a_requiresTailCut BOOLEAN DEFAULT NULL, --needed if any array is passed in a_additionalNotes TEXT DEFAULT '')--optional returns BOOL AS $$ DECLARE curs REFCURSOR; newItemID INTEGER; BEGIN IF off_arr IS NULL THEN IF def_arr IS NULL THEN --Meaning regular item INSERT INTO "Dark Souls Bosses".items (name,notes) VALUES (a_name,a_notes); RETURN TRUE; ELSE --Meaning defensive item IF array_length(def_arr,1) = 4 THEN IF a_weight IS NULL OR a_requiresTailCut IS NULL THEN RAISE 'weight or tailcut args not given'; RETURN FALSE; ELSE INSERT INTO "Dark Souls Bosses".items (name,notes) VALUES (a_name,a_notes); OPEN curs FOR SELECT itemID FROM "Dark Souls Bosses".items WHERE name = a_name; FETCH curs INTO newItemID; INSERT INTO "Dark Souls Bosses".defensiveItems (itemID, physicalDefense, elementalDefense, poise, durability, weight, requiresTailCut,notes) VALUES (CAST(newItemID AS INTEGER),def_arr[1],def_arr[2],def_arr[3],def_arr[4], a_weight,a_requiresTailCut,a_additionalNotes); RETURN TRUE; END IF; ELSE RETURN FALSE; END IF; END IF; ELSE --Meaning offensive item IF array_length(off_arr,1) = 2 THEN IF a_weight IS NULL OR a_requiresTailCut IS NULL THEN RAISE 'weight or tailcut args not given'; RETURN FALSE; ELSE INSERT INTO "Dark Souls Bosses".items (name,notes) VALUES (a_name,a_notes); OPEN curs FOR SELECT itemID FROM "Dark Souls Bosses".items WHERE name = a_name; FETCH curs INTO newItemID; INSERT INTO "Dark Souls Bosses".offensiveItems (itemID, damage, durability, weight, requiresTailCut, notes) VALUES (CAST(newItemID AS INTEGER),off_arr[1],off_arr[2],a_weight,a_requiresTailCut,a_additionalNotes); RETURN TRUE; END IF; ELSE RETURN FALSE; END IF; END IF; END; $$ LANGUAGE PLPGSQL; SELECT addItem('Function Test7','off','{7,0}',NULL,4.2,FALSE,'ありがとう'); --Works SELECT addItem('Function Test8','def',NULL,'{0,0,7,0}',4.2,FALSE,'ありがとう'); --Works SELECT addItem('Function Test9','ありがとう') --Works SELECT addItem('Function Test10','off','{0,0,5,0}',NULL,4.2,FALSE,'ありがとう'); --Doesn't work SELECT addItem('Function Test11','def',NULL,'{0,3}',4.2,FALSE,'ありがとう'); --Doesn't work