CREATE OR REPLACE FUNCTION "buildbuildings"(_colid int, _bdid int, _qty int) RETURNS integer AS $$DECLARE i int; rv int; emp record; col record; bd record; ms record; r record;BEGIN IF _qty <= 0 THEN RETURN -1; END IF; SELECT INTO col * FROM csvdevel.colonies WHERE planetid = _colid; IF NOT FOUND THEN RETURN -2; END IF; SELECT INTO emp * FROM csvdevel.empires WHERE id = col.ownerid; IF NOT FOUND THEN RETURN -3; END IF; SELECT INTO bd * FROM csvdevel.buildings WHERE id = _bdid; IF NOT FOUND OR FOUND AND bd.colonycenter THEN RETURN -4; END IF; rv := 0;-- LOCK TABLE csvdevel.colonies_buildings, csvdevel.colonies_resources IN ACCESS EXCLUSIVE MODE; PERFORM 1 FROM csvdevel.colonies_resources WHERE colonyid = _colid FOR UPDATE; PERFORM 1 FROM csvdevel.empires WHERE id = col.ownerid FOR UPDATE; PERFORM 1 FROM csvdevel.colonies WHERE planetid = _colid FOR UPDATE; <<QTY>> FOR i IN 1.._qty LOOP -- check for money SELECT INTO ms c.freespace,e.credit FROM csvdevel.empires e JOIN csvdevel.colonies c ON e.id = c.ownerid WHERE c.planetid = _colid; RAISE NOTICE '%:% %:%', bd.price, ms.credit, bd.space, ms.freespace; EXIT QTY WHEN ms.freespace < bd.space OR ms.credit < bd.price; -- check the available resources PERFORM 1 FROM csvdevel.building_rscneeds WHERE buildingid=bd.id AND NOT rscid IN (SELECT brsc.rscid FROM csvdevel.building_rscneeds brsc JOIN csvdevel.colonies_resources crsc ON brsc.rscid = crsc.rscid WHERE brsc.buildingid=bd.id AND crsc.colonyid=_colid AND brsc.quantity <= crsc.amount); EXIT WHEN FOUND; -- everything is checked, substract the amounts -- substract the resources UPDATE csvdevel.colonies_resources SET amount=amount-coalesce((SELECT br.quantity FROM building_rscneeds br WHERE br.buildingid=2 AND br.rscid=colonies_resources.rscid),0) WHERE colonyid=_colid; -- substract the credits UPDATE csvdevel.empires SET credit=credit-bd.price WHERE id = emp.id; -- the place will be substracted by a trigger INSERT INTO csvdevel.colonies_buildings (colonyid,buildingid) VALUES (_colid, bd.id); rv := rv + 1; RAISE NOTICE 'built #%', rv; END LOOP QTY; RETURN rv;END;$$ LANGUAGE 'plpgsql';