1 CREATE TABLE project_counts_agg (
2 group_project_id integer NOT NULL,
3 count integer DEFAULT 0 NOT NULL,
4 open_count integer DEFAULT 0
7 CREATE VIEW project_group_list_vw AS SELECT * FROM project_group_list NATURAL JOIN project_counts_agg;
9 INSERT INTO project_counts_agg
10 SELECT group_project_id,
11 (SELECT count(*) FROM project_task WHERE status_id != 3 AND
12 project_task.group_project_id=project_group_list.group_project_id),
13 (SELECT count(*) FROM project_task WHERE status_id = 1 AND
14 project_task.group_project_id=project_group_list.group_project_id)
15 FROM project_group_list;
17 CREATE FUNCTION projectgrouplist_insert_agg () RETURNS opaque AS '
19 INSERT INTO project_counts_agg (group_project_id,count,open_count)
20 VALUES (NEW.group_project_id,0,0);
26 CREATE FUNCTION projectgroup_update_agg () RETURNS opaque AS '
29 -- see if they are moving to a new subproject
30 -- if so, its a more complex operation
32 IF NEW.group_project_id <> OLD.group_project_id THEN
34 -- transferred tasks always have a status of 1
35 -- so we will increment the new subprojects sums
37 IF OLD.status_id=3 THEN
38 -- No need to decrement counters on old tracker
40 IF OLD.status_id=2 THEN
41 UPDATE project_counts_agg SET count=count-1
42 WHERE group_project_id=OLD.group_project_id;
44 IF OLD.status_id=1 THEN
45 UPDATE project_counts_agg SET count=count-1,open_count=open_count-1
46 WHERE group_project_id=OLD.group_project_id;
51 IF NEW.status_id=3 THEN
54 IF NEW.status_id=2 THEN
55 UPDATE project_counts_agg SET count=count+1
56 WHERE group_project_id=NEW.group_project_id;
58 IF NEW.status_id=1 THEN
59 UPDATE project_counts_agg SET count=count+1, open_count=open_count+1
60 WHERE group_project_id=NEW.group_project_id;
66 -- just need to evaluate the status flag and
67 -- increment/decrement the counter as necessary
69 IF NEW.status_id <> OLD.status_id THEN
70 IF NEW.status_id = 1 THEN
71 IF OLD.status_id=2 THEN
72 UPDATE project_counts_agg SET open_count=open_count+1
73 WHERE group_project_id=NEW.group_project_id;
75 IF OLD.status_id=3 THEN
76 UPDATE project_counts_agg SET open_count=open_count+1, count=count+1
77 WHERE group_project_id=NEW.group_project_id;
81 IF NEW.status_id = 2 THEN
82 IF OLD.status_id=1 THEN
83 UPDATE project_counts_agg SET open_count=open_count-1
84 WHERE group_project_id=NEW.group_project_id;
86 IF OLD.status_id=3 THEN
87 UPDATE project_counts_agg SET count=count+1
88 WHERE group_project_id=NEW.group_project_id;
92 IF NEW.status_id = 3 THEN
93 IF OLD.status_id=2 THEN
94 UPDATE project_counts_agg SET count=count-1
95 WHERE group_project_id=NEW.group_project_id;
97 IF OLD.status_id=1 THEN
98 UPDATE project_counts_agg SET open_count=open_count-1,count=count-1
99 WHERE group_project_id=NEW.group_project_id;
109 ' LANGUAGE 'plpgsql';
111 CREATE TRIGGER projectgrouplist_insert_trig AFTER INSERT ON project_group_list
112 FOR EACH ROW EXECUTE PROCEDURE projectgrouplist_insert_agg ();
115 CREATE TRIGGER projectgroup_update_trig AFTER UPDATE ON project_task
116 FOR EACH ROW EXECUTE PROCEDURE projectgroup_update_agg ();