3 --- This function updates the project_sums_agg count for a given type.
5 CREATE FUNCTION "project_sums" () RETURNS OPAQUE AS '
11 --- Get number of things this group has now
13 IF TG_ARGV[0]=\'surv\' THEN
14 IF TG_OP=\'DELETE\' THEN
15 SELECT INTO num count(*) FROM surveys WHERE OLD.group_id=group_id AND is_active=1;
16 curr_group := OLD.group_id;
18 SELECT INTO num count(*) FROM surveys WHERE NEW.group_id=group_id AND is_active=1;
19 curr_group := NEW.group_id;
22 IF TG_ARGV[0]=\'mail\' THEN
23 IF TG_OP=\'DELETE\' THEN
24 SELECT INTO num count(*) FROM mail_group_list WHERE OLD.group_id=group_id AND is_public=1;
25 curr_group := OLD.group_id;
27 SELECT INTO num count(*) FROM mail_group_list WHERE NEW.group_id=group_id AND is_public=1;
28 curr_group := NEW.group_id;
31 IF TG_ARGV[0]=\'fmsg\' THEN
32 IF TG_OP=\'DELETE\' THEN
33 SELECT INTO curr_group group_id FROM forum_group_list WHERE OLD.group_forum_id=group_forum_id;
34 SELECT INTO num count(*) FROM forum, forum_group_list WHERE forum.group_forum_id=forum_group_list.group_forum_id AND forum_group_list.is_public=1 AND forum_group_list.group_id=curr_group;
36 SELECT INTO curr_group group_id FROM forum_group_list WHERE NEW.group_forum_id=group_forum_id;
37 SELECT INTO num count(*) FROM forum, forum_group_list WHERE forum.group_forum_id=forum_group_list.group_forum_id AND forum_group_list.is_public=1 AND forum_group_list.group_id=curr_group;
40 IF TG_ARGV[0]=\'fora\' THEN
41 IF TG_OP=\'DELETE\' THEN
42 SELECT INTO num count(*) FROM forum_group_list WHERE OLD.group_id=group_id AND is_public=1;
43 curr_group = OLD.group_id;
44 --- also need to update message count
45 DELETE FROM project_sums_agg WHERE group_id=OLD.group_id AND type=\'fmsg\';
46 INSERT INTO project_sums_agg
47 SELECT forum_group_list.group_id,\'fmsg\'::text AS type, count(forum.msg_id) AS count
48 FROM forum, forum_group_list
49 WHERE forum.group_forum_id=forum_group_list.group_forum_id AND forum_group_list.is_public=1 GROUP BY group_id,type;
51 SELECT INTO num count(*) FROM forum_group_list WHERE NEW.group_id=group_id AND is_public=1;
52 curr_group = NEW.group_id;
53 --- fora do not get deleted... they get their status set to 9
54 IF NEW.is_public=9 THEN
55 --- also need to update message count
56 DELETE FROM project_sums_agg WHERE group_id=NEW.group_id AND type=\'fmsg\';
57 INSERT INTO project_sums_agg
58 SELECT forum_group_list.group_id,\'fmsg\'::text AS type, count(forum.msg_id) AS count
59 FROM forum, forum_group_list
60 WHERE forum.group_forum_id=forum_group_list.group_forum_id AND forum_group_list.is_public=1 GROUP BY group_id,type;
65 --- See if this group already has a row in project_sums_agg for these things
67 PERFORM * FROM project_sums_agg WHERE curr_group=group_id AND type=TG_ARGV[0];
71 --- Create row for this group
73 INSERT INTO project_sums_agg
74 VALUES (curr_group, TG_ARGV[0], num);
79 UPDATE project_sums_agg SET count=num
80 WHERE curr_group=group_id AND type=TG_ARGV[0];
83 IF TG_OP=\'DELETE\' THEN
91 CREATE TRIGGER "surveys_agg_trig" AFTER INSERT OR DELETE OR UPDATE ON "surveys" FOR EACH ROW EXECUTE PROCEDURE project_sums('surv');
93 CREATE TRIGGER "mail_agg_trig" AFTER INSERT OR DELETE OR UPDATE ON "mail_group_list" FOR EACH ROW EXECUTE PROCEDURE project_sums('mail');
95 CREATE TRIGGER "fmsg_agg_trig" AFTER INSERT OR DELETE OR UPDATE ON "forum" FOR EACH ROW EXECUTE PROCEDURE project_sums('fmsg');
97 CREATE TRIGGER "fora_agg_trig" AFTER INSERT OR DELETE OR UPDATE ON "forum_group_list" FOR EACH ROW EXECUTE PROCEDURE project_sums('fora');