1 CREATE TABLE deleted_mailing_lists (
2 mailing_list_name varchar(30),
7 INSERT INTO deleted_mailing_lists (mailing_list_name,
8 delete_date,isdeleted) SELECT
9 list_name,(EXTRACT(EPOCH FROM now())::integer),0
10 FROM mail_group_list WHERE status=9;
12 DELETE FROM mail_group_list WHERE status=9;
14 CREATE TABLE deleted_groups (
15 unix_group_name varchar(30),
19 UPDATE groups SET status='H' WHERE status='D';
21 --- Drop the triggers and function created by 20030109.sql.
22 --- 20030109.sql did not work for people using pgsql 7.1.x.
25 DROP TRIGGER surveys_agg_trig ON surveys;
26 DROP TRIGGER mail_agg_trig ON mail_group_list;
27 DROP TRIGGER fmsg_agg_trig ON forum;
28 DROP TRIGGER fora_agg_trig ON forum_group_list;
29 DROP FUNCTION project_sums();
32 --- Reimplement functionality of 20030109.sql in a way that works
33 --- for all pgsql > 7.0. Users of pgsql > 7.3 will notice:
35 --- NOTICE: CreateTrigger: changing return type of function project_sums() from OPAQUE to TRIGGER
37 --- that is safe to ignore.
41 --- This function updates the project_sums_agg count for a given type.
43 CREATE FUNCTION "project_sums" () RETURNS OPAQUE AS '
50 --- Get number of things this group has now
52 IF TG_ARGV[0]=\'surv\' THEN
53 IF TG_OP=\'DELETE\' THEN
54 SELECT INTO num count(*) FROM surveys WHERE OLD.group_id=group_id AND is_active=1;
55 curr_group := OLD.group_id;
57 SELECT INTO num count(*) FROM surveys WHERE NEW.group_id=group_id AND is_active=1;
58 curr_group := NEW.group_id;
61 IF TG_ARGV[0]=\'mail\' THEN
62 IF TG_OP=\'DELETE\' THEN
63 SELECT INTO num count(*) FROM mail_group_list WHERE OLD.group_id=group_id AND is_public=1;
64 curr_group := OLD.group_id;
66 SELECT INTO num count(*) FROM mail_group_list WHERE NEW.group_id=group_id AND is_public=1;
67 curr_group := NEW.group_id;
70 IF TG_ARGV[0]=\'fmsg\' THEN
71 IF TG_OP=\'DELETE\' THEN
72 SELECT INTO curr_group group_id FROM forum_group_list WHERE OLD.group_forum_id=group_forum_id;
73 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;
75 SELECT INTO curr_group group_id FROM forum_group_list WHERE NEW.group_forum_id=group_forum_id;
76 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;
79 IF TG_ARGV[0]=\'fora\' THEN
80 IF TG_OP=\'DELETE\' THEN
81 SELECT INTO num count(*) FROM forum_group_list WHERE OLD.group_id=group_id AND is_public=1;
82 curr_group = OLD.group_id;
83 --- also need to update message count
84 DELETE FROM project_sums_agg WHERE group_id=OLD.group_id AND type=\'fmsg\';
85 INSERT INTO project_sums_agg
86 SELECT OLD.group_id,\'fmsg\'::text AS type, count(forum.msg_id) AS count
87 FROM forum, forum_group_list
88 WHERE forum.group_forum_id=forum_group_list.group_forum_id AND forum_group_list.is_public=1 AND forum_group_list.group_id=OLD.group_id GROUP BY group_id,type;
90 SELECT INTO num count(*) FROM forum_group_list WHERE NEW.group_id=group_id AND is_public=1;
91 curr_group = NEW.group_id;
95 --- See if this group already has a row in project_sums_agg for these things
97 SELECT INTO found count(group_id) FROM project_sums_agg WHERE curr_group=group_id AND type=TG_ARGV[0];
101 --- Create row for this group
103 INSERT INTO project_sums_agg
104 VALUES (curr_group, TG_ARGV[0], num);
109 UPDATE project_sums_agg SET count=num
110 WHERE curr_group=group_id AND type=TG_ARGV[0];
113 IF TG_OP=\'DELETE\' THEN
119 ' LANGUAGE 'plpgsql';
121 CREATE TRIGGER "surveys_agg_trig" AFTER INSERT OR DELETE OR UPDATE ON "surveys" FOR EACH ROW EXECUTE PROCEDURE project_sums('surv');
123 CREATE TRIGGER "mail_agg_trig" AFTER INSERT OR DELETE OR UPDATE ON "mail_group_list" FOR EACH ROW EXECUTE PROCEDURE project_sums('mail');
125 CREATE TRIGGER "fmsg_agg_trig" AFTER INSERT OR DELETE OR UPDATE ON "forum" FOR EACH ROW EXECUTE PROCEDURE project_sums('fmsg');
127 CREATE TRIGGER "fora_agg_trig" AFTER INSERT OR DELETE OR UPDATE ON "forum_group_list" FOR EACH ROW EXECUTE PROCEDURE project_sums('fora');