CREATE TABLE project_counts_agg ( group_project_id integer NOT NULL, count integer DEFAULT 0 NOT NULL, open_count integer DEFAULT 0 ); CREATE VIEW project_group_list_vw AS SELECT * FROM project_group_list NATURAL JOIN project_counts_agg; INSERT INTO project_counts_agg SELECT group_project_id, (SELECT count(*) FROM project_task WHERE status_id != 3 AND project_task.group_project_id=project_group_list.group_project_id), (SELECT count(*) FROM project_task WHERE status_id = 1 AND project_task.group_project_id=project_group_list.group_project_id) FROM project_group_list; CREATE FUNCTION projectgrouplist_insert_agg () RETURNS opaque AS ' BEGIN INSERT INTO project_counts_agg (group_project_id,count,open_count) VALUES (NEW.group_project_id,0,0); RETURN NEW; END; ' LANGUAGE 'plpgsql'; CREATE FUNCTION projectgroup_update_agg () RETURNS opaque AS ' BEGIN -- -- see if they are moving to a new subproject -- if so, its a more complex operation -- IF NEW.group_project_id <> OLD.group_project_id THEN -- -- transferred tasks always have a status of 1 -- so we will increment the new subprojects sums -- IF OLD.status_id=3 THEN -- No need to decrement counters on old tracker ELSE IF OLD.status_id=2 THEN UPDATE project_counts_agg SET count=count-1 WHERE group_project_id=OLD.group_project_id; ELSE IF OLD.status_id=1 THEN UPDATE project_counts_agg SET count=count-1,open_count=open_count-1 WHERE group_project_id=OLD.group_project_id; END IF; END IF; END IF; IF NEW.status_id=3 THEN --DO NOTHING ELSE IF NEW.status_id=2 THEN UPDATE project_counts_agg SET count=count+1 WHERE group_project_id=NEW.group_project_id; ELSE IF NEW.status_id=1 THEN UPDATE project_counts_agg SET count=count+1, open_count=open_count+1 WHERE group_project_id=NEW.group_project_id; END IF; END IF; END IF; ELSE -- -- just need to evaluate the status flag and -- increment/decrement the counter as necessary -- IF NEW.status_id <> OLD.status_id THEN IF NEW.status_id = 1 THEN IF OLD.status_id=2 THEN UPDATE project_counts_agg SET open_count=open_count+1 WHERE group_project_id=NEW.group_project_id; ELSE IF OLD.status_id=3 THEN UPDATE project_counts_agg SET open_count=open_count+1, count=count+1 WHERE group_project_id=NEW.group_project_id; END IF; END IF; ELSE IF NEW.status_id = 2 THEN IF OLD.status_id=1 THEN UPDATE project_counts_agg SET open_count=open_count-1 WHERE group_project_id=NEW.group_project_id; ELSE IF OLD.status_id=3 THEN UPDATE project_counts_agg SET count=count+1 WHERE group_project_id=NEW.group_project_id; END IF; END IF; ELSE IF NEW.status_id = 3 THEN IF OLD.status_id=2 THEN UPDATE project_counts_agg SET count=count-1 WHERE group_project_id=NEW.group_project_id; ELSE IF OLD.status_id=1 THEN UPDATE project_counts_agg SET open_count=open_count-1,count=count-1 WHERE group_project_id=NEW.group_project_id; END IF; END IF; END IF; END IF; END IF; END IF; END IF; RETURN NEW; END; ' LANGUAGE 'plpgsql'; CREATE TRIGGER projectgrouplist_insert_trig AFTER INSERT ON project_group_list FOR EACH ROW EXECUTE PROCEDURE projectgrouplist_insert_agg (); CREATE TRIGGER projectgroup_update_trig AFTER UPDATE ON project_task FOR EACH ROW EXECUTE PROCEDURE projectgroup_update_agg ();