1 SET client_min_messages TO warning;
3 DROP FUNCTION update_vectors() CASCADE;
5 CREATE OR REPLACE FUNCTION update_vectors() RETURNS TRIGGER AS '
9 table_name := TG_ARGV[0];
10 -- **** artifact table ****
11 IF table_name = ''artifact'' THEN
12 IF TG_OP = ''INSERT'' THEN
13 INSERT INTO artifact_idx (artifact_id, group_artifact_id, vectors) VALUES (NEW.artifact_id, NEW.group_artifact_id, to_tsvector(coalesce(NEW.details,\'\') ||\' \'|| coalesce(NEW.summary,\'\')));
14 ELSIF TG_OP = ''UPDATE'' THEN
15 UPDATE artifact_idx SET group_artifact_id=NEW.group_artifact_id, vectors=to_tsvector(coalesce(NEW.details,\'\') ||\' \'|| coalesce(NEW.summary,\'\')) WHERE artifact_id=NEW.artifact_id;
16 ELSIF TG_OP = ''DELETE'' THEN
17 DELETE FROM artifact_idx WHERE artifact_id=OLD.artifact_id;
19 -- **** artifact_message table ****
20 ELSIF table_name = ''artifact_message'' THEN
21 IF TG_OP = ''INSERT'' THEN
22 INSERT INTO artifact_message_idx (id, artifact_id, vectors) VALUES (NEW.id, NEW.artifact_id, to_tsvector(coalesce(NEW.body,\'\')));
23 ELSIF TG_OP = ''UPDATE'' THEN
24 UPDATE artifact_message_idx SET artifact_id=NEW.artifact_id, vectors=to_tsvector(coalesce(NEW.body,\'\')) WHERE id=NEW.id;
25 ELSIF TG_OP = ''DELETE'' THEN
26 DELETE FROM artifact_message_idx WHERE id=OLD.id;
28 -- **** doc_data table ****
29 ELSIF table_name = ''doc_data'' THEN
30 IF TG_OP = ''INSERT'' THEN
31 INSERT INTO doc_data_idx (docid, group_id, vectors) VALUES (NEW.docid, NEW.group_id, to_tsvector(coalesce(NEW.title,\'\') ||\' \'|| coalesce(NEW.description,\'\')));
32 ELSIF TG_OP = ''UPDATE'' THEN
33 UPDATE doc_data_idx SET group_id=NEW.group_id, vectors=to_tsvector(coalesce(NEW.title,\'\') ||\' \'|| coalesce(NEW.description,\'\')) WHERE docid=NEW.docid;
34 ELSIF TG_OP = ''DELETE'' THEN
35 DELETE FROM doc_data_idx WHERE docid=OLD.docid;
37 -- **** forum table ****
38 ELSIF table_name = ''forum'' THEN
39 IF TG_OP = ''INSERT'' THEN
40 INSERT INTO forum_idx (msg_id, group_id, vectors) (SELECT f.msg_id, g.group_id, to_tsvector(coalesce(f.subject,\'\') ||\' \'||
41 coalesce(f.body,\'\')) AS vectors FROM forum f, forum_group_list g WHERE f.group_forum_id = g.group_forum_id AND f.msg_id = NEW.msg_id);
42 ELSIF TG_OP = ''UPDATE'' THEN
43 UPDATE forum_idx SET vectors=to_tsvector(coalesce(NEW.subject,\'\') ||\' \'|| coalesce(NEW.body,\'\')) WHERE msg_id=NEW.msg_id;
44 ELSIF TG_OP = ''DELETE'' THEN
45 DELETE FROM forum_idx WHERE msg_id=OLD.msg_id;
47 -- **** frs_file table ****
48 ELSIF table_name = ''frs_file'' THEN
49 IF TG_OP = ''INSERT'' THEN
50 INSERT INTO frs_file_idx (file_id, release_id, vectors) VALUES (NEW.file_id, NEW.release_id, to_tsvector(coalesce(NEW.filename,\'\')));
51 ELSIF TG_OP = ''UPDATE'' THEN
52 UPDATE frs_file_idx SET vectors=to_tsvector(coalesce(NEW.filename,\'\')), release_id=NEW.release_id WHERE file_id=NEW.file_id;
53 ELSIF TG_OP = ''DELETE'' THEN
54 DELETE FROM frs_file_idx WHERE file_id=OLD.file_id;
56 -- **** frs_release table ****
57 ELSIF table_name = ''frs_release'' THEN
58 IF TG_OP = ''INSERT'' THEN
59 INSERT INTO frs_release_idx (release_id, vectors) VALUES (NEW.release_id, to_tsvector(coalesce(NEW.changes,\'\') ||\' \'|| coalesce(NEW.notes,\'\') ||\' \'|| coalesce(NEW.name,\'\')));
60 ELSIF TG_OP = ''UPDATE'' THEN
61 UPDATE frs_release_idx SET vectors=to_tsvector(coalesce(NEW.changes,\'\') ||\' \'|| coalesce(NEW.notes,\'\') ||\' \'|| coalesce(NEW.name,\'\')) WHERE release_id=NEW.release_id;
62 ELSIF TG_OP = ''DELETE'' THEN
63 DELETE FROM frs_release_idx WHERE release_id=OLD.release_id;
64 DELETE FROM frs_file_idx WHERE release_id=OLD.release_id;
66 -- **** groups table ****
67 ELSIF table_name = ''groups'' THEN
68 IF TG_OP = ''INSERT'' THEN
69 INSERT INTO groups_idx (group_id, vectors) VALUES (NEW.group_id, to_tsvector(coalesce(NEW.group_name,\'\') ||\' \'|| coalesce(NEW.short_description,\'\') ||\' \'|| coalesce(NEW.unix_group_name,\'\')));
70 ELSIF TG_OP = ''UPDATE'' THEN
71 UPDATE groups_idx SET vectors=to_tsvector(coalesce(NEW.group_name,\'\') ||\' \'|| coalesce(NEW.short_description,\'\') ||\' \'|| coalesce(NEW.unix_group_name,\'\')) WHERE group_id=NEW.group_id;
72 ELSIF TG_OP = ''DELETE'' THEN
73 DELETE FROM groups_idx WHERE group_id=OLD.group_id;
75 -- **** news_bytes table ****
76 ELSIF table_name = ''news_bytes'' THEN
77 IF TG_OP = ''INSERT'' THEN
78 INSERT INTO news_bytes_idx (id, vectors) VALUES (NEW.id, to_tsvector(coalesce(NEW.summary,\'\') ||\' \'|| coalesce(NEW.details,\'\')));
79 ELSIF TG_OP = ''UPDATE'' THEN
80 UPDATE news_bytes_idx SET vectors=to_tsvector(coalesce(NEW.summary,\'\') ||\' \'|| coalesce(NEW.details,\'\')) WHERE id=NEW.id;
81 ELSIF TG_OP = ''DELETE'' THEN
82 DELETE FROM news_bytes_idx WHERE id=OLD.id;
84 -- **** project_task table ****
85 ELSIF table_name = ''project_task'' THEN
86 IF TG_OP = ''INSERT'' THEN
87 INSERT INTO project_task_idx (project_task_id, vectors) VALUES (NEW.project_task_id, to_tsvector(coalesce(NEW.summary,\'\') ||\' \'|| coalesce(NEW.details,\'\')));
88 ELSIF TG_OP = ''UPDATE'' THEN
89 UPDATE project_task_idx SET vectors=to_tsvector(coalesce(NEW.summary,\'\') ||\' \'|| coalesce(NEW.details,\'\')) WHERE project_task_id=NEW.project_task_id;
90 ELSIF TG_OP = ''DELETE'' THEN
91 DELETE FROM project_task_idx WHERE project_task_id=OLD.project_task_id;
93 -- **** skills_data table ****
94 ELSIF table_name = ''skills_data'' THEN
95 IF TG_OP = ''INSERT'' THEN
96 INSERT INTO skills_data_idx (skills_data_id, vectors) VALUES (NEW.skill_data_id, to_tsvector(coalesce(NEW.title,\'\') ||\' \'|| coalesce(NEW.keywords,\'\')));
97 ELSIF TG_OP = ''UPDATE'' THEN
98 UPDATE skills_data_idx SET vectors=to_tsvector(coalesce(NEW.title,\'\') ||\' \'|| coalesce(NEW.keywords,\'\')) WHERE skills_data_id=NEW.skills_data_id;
99 ELSIF TG_OP = ''DELETE'' THEN
100 DELETE FROM skills_data_idx WHERE skills_data_id=OLD.skills_data_id;
102 -- **** users table ****
103 ELSIF table_name = ''users'' THEN
104 IF TG_OP = ''INSERT'' THEN
105 INSERT INTO users_idx (user_id, vectors) VALUES (NEW.user_id, to_tsvector(coalesce(NEW.user_name,\'\') ||\' \'|| coalesce(NEW.realname,\'\')));
106 ELSIF TG_OP = ''UPDATE'' THEN
107 UPDATE users_idx SET vectors=to_tsvector(coalesce(NEW.user_name,\'\') ||\' \'|| coalesce(NEW.realname,\'\')) WHERE user_id=NEW.user_id;
108 ELSIF TG_OP = ''DELETE'' THEN
109 DELETE FROM users_idx WHERE user_id=OLD.user_id;
117 -- ********** Set up triggers **********
119 CREATE TRIGGER artifact_ts_update AFTER UPDATE OR INSERT OR DELETE ON artifact
120 FOR EACH ROW EXECUTE PROCEDURE update_vectors('artifact');
122 CREATE TRIGGER doc_data_ts_update AFTER UPDATE OR INSERT OR DELETE ON doc_data
123 FOR EACH ROW EXECUTE PROCEDURE update_vectors('doc_data');
125 CREATE TRIGGER forum_update AFTER UPDATE OR INSERT OR DELETE ON forum
126 FOR EACH ROW EXECUTE PROCEDURE update_vectors('forum');
128 CREATE TRIGGER frs_file_ts_update AFTER UPDATE OR INSERT OR DELETE ON frs_file
129 FOR EACH ROW EXECUTE PROCEDURE update_vectors('frs_file');
131 CREATE TRIGGER frs_release_ts_update AFTER UPDATE OR INSERT OR DELETE ON frs_release
132 FOR EACH ROW EXECUTE PROCEDURE update_vectors('frs_release');
134 CREATE TRIGGER groups_ts_update AFTER UPDATE OR INSERT OR DELETE ON groups
135 FOR EACH ROW EXECUTE PROCEDURE update_vectors('groups');
137 CREATE TRIGGER news_bytes_ts_update AFTER UPDATE OR INSERT OR DELETE ON news_bytes
138 FOR EACH ROW EXECUTE PROCEDURE update_vectors('news_bytes');
140 CREATE TRIGGER project_task_ts_update AFTER UPDATE OR INSERT OR DELETE ON project_task
141 FOR EACH ROW EXECUTE PROCEDURE update_vectors('project_task');
143 CREATE TRIGGER skills_data_ts_update AFTER UPDATE OR INSERT OR DELETE ON skills_data
144 FOR EACH ROW EXECUTE PROCEDURE update_vectors('skills_data');
146 CREATE TRIGGER users_ts_update AFTER UPDATE OR INSERT OR DELETE ON users
147 FOR EACH ROW EXECUTE PROCEDURE update_vectors('users');