rev |
line source |
jbe@478
|
1 BEGIN;
|
jbe@478
|
2
|
jbe@478
|
3 CREATE OR REPLACE VIEW "liquid_feedback_version" AS
|
jbe@478
|
4 SELECT * FROM (VALUES ('3.2.0', 3, 2, 0))
|
jbe@478
|
5 AS "subquery"("string", "major", "minor", "revision");
|
jbe@478
|
6
|
jbe@478
|
7 ALTER TABLE "member" ADD COLUMN "disable_notifications" BOOLEAN NOT NULL DEFAULT FALSE;
|
jbe@492
|
8 ALTER TABLE "member" ADD COLUMN "notification_counter" INT4 NOT NULL DEFAULT 1;
|
jbe@492
|
9 ALTER TABLE "member" ADD COLUMN "notification_sample_size" INT4 NOT NULL DEFAULT 3;
|
jbe@492
|
10 ALTER TABLE "member" ADD COLUMN "notification_dow" INT4 CHECK ("notification_dow" BETWEEN 0 AND 6);
|
jbe@492
|
11 ALTER TABLE "member" ADD COLUMN "notification_hour" INT4 CHECK ("notification_hour" BETWEEN 0 AND 23);
|
jbe@478
|
12
|
jbe@478
|
13 UPDATE "member" SET "disable_notifications" = TRUE WHERE "notify_level" = 'none';
|
jbe@478
|
14
|
jbe@478
|
15 CREATE TABLE "subscription" (
|
jbe@478
|
16 PRIMARY KEY ("member_id", "unit_id"),
|
jbe@478
|
17 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
|
jbe@478
|
18 "unit_id" INT4 REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE );
|
jbe@478
|
19 CREATE INDEX "subscription_unit_id_idx" ON "subscription" ("unit_id");
|
jbe@492
|
20
|
jbe@492
|
21 COMMENT ON TABLE "subscription" IS 'An entry in this table denotes that the member wishes to receive notifications regardless of his/her privileges in the given unit';
|
jbe@492
|
22
|
jbe@492
|
23 CREATE TABLE "ignored_area" (
|
jbe@492
|
24 PRIMARY KEY ("member_id", "area_id"),
|
jbe@492
|
25 "member_id" INT4 REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
|
jbe@492
|
26 "area_id" INT4 REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE );
|
jbe@492
|
27 CREATE INDEX "ignored_area_area_id_idx" ON "ignored_area" ("area_id");
|
jbe@492
|
28
|
jbe@492
|
29 COMMENT ON TABLE "ignored_area" IS 'An entry in this table denotes that the member does not wish to receive notifications for the given subject area unless he/she declared interested in a particular issue';
|
jbe@492
|
30
|
jbe@492
|
31 CREATE TABLE "initiative_notification_sent" (
|
jbe@492
|
32 PRIMARY KEY ("member_id", "initiative_id"),
|
jbe@492
|
33 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
|
jbe@492
|
34 "initiative_id" INT4 REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
|
jbe@492
|
35 "last_draft_id" INT8 NOT NULL,
|
jbe@495
|
36 "last_suggestion_id" INT8 );
|
jbe@492
|
37 CREATE INDEX "initiative_notification_sent_initiative_idx" ON "initiative_notification_sent" ("initiative_id");
|
jbe@478
|
38
|
jbe@496
|
39 CREATE TABLE "newsletter" (
|
jbe@496
|
40 "id" SERIAL4 PRIMARY KEY,
|
jbe@496
|
41 "published" TIMESTAMPTZ NOT NULL,
|
jbe@496
|
42 "unit_id" INT4 REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
|
jbe@496
|
43 "include_all_members" BOOLEAN NOT NULL,
|
jbe@496
|
44 "sent" TIMESTAMPTZ,
|
jbe@496
|
45 "subject" TEXT NOT NULL,
|
jbe@496
|
46 "content" TEXT NOT NULL );
|
jbe@496
|
47 CREATE INDEX "newsletter_unit_id_idx" ON "newsletter" ("unit_id", "published");
|
jbe@496
|
48 CREATE INDEX "newsletter_all_units_published_idx" ON "newsletter" ("published") WHERE "unit_id" ISNULL;
|
jbe@496
|
49 CREATE INDEX "newsletter_published_idx" ON "newsletter" ("published");
|
jbe@496
|
50
|
jbe@478
|
51 CREATE VIEW "updated_initiative" AS
|
jbe@478
|
52 SELECT
|
jbe@492
|
53 "supporter"."member_id" AS "seen_by_member_id",
|
jbe@478
|
54 TRUE AS "supported",
|
jbe@478
|
55 EXISTS (
|
jbe@478
|
56 SELECT NULL FROM "draft"
|
jbe@478
|
57 WHERE "draft"."initiative_id" = "initiative"."id"
|
jbe@478
|
58 AND "draft"."id" > "supporter"."draft_id"
|
jbe@478
|
59 ) AS "new_draft",
|
jbe@478
|
60 ( SELECT count(1) FROM "suggestion"
|
jbe@492
|
61 LEFT JOIN "opinion" ON
|
jbe@492
|
62 "opinion"."member_id" = "supporter"."member_id" AND
|
jbe@492
|
63 "opinion"."suggestion_id" = "suggestion"."id"
|
jbe@478
|
64 WHERE "suggestion"."initiative_id" = "initiative"."id"
|
jbe@492
|
65 AND "opinion"."member_id" ISNULL
|
jbe@478
|
66 AND COALESCE(
|
jbe@492
|
67 "suggestion"."id" > "sent"."last_suggestion_id",
|
jbe@478
|
68 TRUE
|
jbe@478
|
69 )
|
jbe@478
|
70 ) AS "new_suggestion_count",
|
jbe@478
|
71 FALSE AS "featured",
|
jbe@478
|
72 NOT EXISTS (
|
jbe@478
|
73 SELECT NULL FROM "initiative" AS "better_initiative"
|
jbe@478
|
74 WHERE
|
jbe@484
|
75 "better_initiative"."issue_id" = "initiative"."issue_id"
|
jbe@484
|
76 AND
|
jbe@484
|
77 ( COALESCE("better_initiative"."harmonic_weight", -1),
|
jbe@484
|
78 -"better_initiative"."id" ) >
|
jbe@484
|
79 ( COALESCE("initiative"."harmonic_weight", -1),
|
jbe@485
|
80 -"initiative"."id" )
|
jbe@478
|
81 ) AS "leading",
|
jbe@478
|
82 "initiative".*
|
jbe@492
|
83 FROM "supporter" JOIN "initiative"
|
jbe@492
|
84 ON "supporter"."initiative_id" = "initiative"."id"
|
jbe@492
|
85 LEFT JOIN "initiative_notification_sent" AS "sent"
|
jbe@492
|
86 ON "sent"."member_id" = "supporter"."member_id"
|
jbe@492
|
87 AND "sent"."initiative_id" = "initiative"."id"
|
jbe@478
|
88 JOIN "issue" ON "issue"."id" = "initiative"."issue_id"
|
jbe@480
|
89 WHERE "issue"."state" IN ('admission', 'discussion')
|
jbe@478
|
90 AND (
|
jbe@478
|
91 EXISTS (
|
jbe@478
|
92 SELECT NULL FROM "draft"
|
jbe@478
|
93 WHERE "draft"."initiative_id" = "initiative"."id"
|
jbe@478
|
94 AND "draft"."id" > "supporter"."draft_id"
|
jbe@478
|
95 ) OR EXISTS (
|
jbe@478
|
96 SELECT NULL FROM "suggestion"
|
jbe@492
|
97 LEFT JOIN "opinion" ON
|
jbe@492
|
98 "opinion"."member_id" = "supporter"."member_id" AND
|
jbe@492
|
99 "opinion"."suggestion_id" = "suggestion"."id"
|
jbe@478
|
100 WHERE "suggestion"."initiative_id" = "initiative"."id"
|
jbe@492
|
101 AND "opinion"."member_id" ISNULL
|
jbe@478
|
102 AND COALESCE(
|
jbe@492
|
103 "suggestion"."id" > "sent"."last_suggestion_id",
|
jbe@478
|
104 TRUE
|
jbe@478
|
105 )
|
jbe@478
|
106 )
|
jbe@478
|
107 );
|
jbe@478
|
108
|
jbe@478
|
109 CREATE FUNCTION "featured_initiative"
|
jbe@478
|
110 ( "member_id_p" "member"."id"%TYPE,
|
jbe@478
|
111 "area_id_p" "area"."id"%TYPE )
|
jbe@478
|
112 RETURNS SETOF "initiative"
|
jbe@478
|
113 LANGUAGE 'plpgsql' STABLE AS $$
|
jbe@478
|
114 DECLARE
|
jbe@482
|
115 "member_row" "member"%ROWTYPE;
|
jbe@478
|
116 "member_id_v" "member"."id"%TYPE;
|
jbe@478
|
117 "seed_v" TEXT;
|
jbe@478
|
118 "result_row" "initiative"%ROWTYPE;
|
jbe@478
|
119 "match_v" BOOLEAN;
|
jbe@478
|
120 "initiative_id_ary" INT4[]; --"initiative"."id"%TYPE[]
|
jbe@478
|
121 BEGIN
|
jbe@482
|
122 SELECT INTO "member_row" * FROM "member" WHERE "id" = "member_id_p";
|
jbe@478
|
123 "initiative_id_ary" := '{}';
|
jbe@478
|
124 LOOP
|
jbe@478
|
125 "match_v" := FALSE;
|
jbe@478
|
126 FOR "member_id_v", "seed_v" IN
|
jbe@478
|
127 SELECT * FROM (
|
jbe@478
|
128 SELECT DISTINCT
|
jbe@478
|
129 "supporter"."member_id",
|
jbe@482
|
130 md5("member_id_p" || '-' || "member_row"."notification_counter" || '-' || "area_id_p" || '-' || "supporter"."member_id") AS "seed"
|
jbe@478
|
131 FROM "supporter"
|
jbe@478
|
132 JOIN "member" ON "member"."id" = "supporter"."member_id"
|
jbe@478
|
133 JOIN "initiative" ON "initiative"."id" = "supporter"."initiative_id"
|
jbe@478
|
134 JOIN "issue" ON "issue"."id" = "initiative"."issue_id"
|
jbe@478
|
135 WHERE "supporter"."member_id" != "member_id_p"
|
jbe@478
|
136 AND "issue"."area_id" = "area_id_p"
|
jbe@478
|
137 AND "issue"."state" IN ('admission', 'discussion', 'verification')
|
jbe@478
|
138 ) AS "subquery"
|
jbe@478
|
139 ORDER BY "seed"
|
jbe@478
|
140 LOOP
|
jbe@478
|
141 SELECT "initiative".* INTO "result_row"
|
jbe@478
|
142 FROM "initiative"
|
jbe@478
|
143 JOIN "issue" ON "issue"."id" = "initiative"."issue_id"
|
jbe@478
|
144 JOIN "supporter" ON "supporter"."initiative_id" = "initiative"."id"
|
jbe@478
|
145 LEFT JOIN "supporter" AS "self_support" ON
|
jbe@478
|
146 "self_support"."initiative_id" = "initiative"."id" AND
|
jbe@478
|
147 "self_support"."member_id" = "member_id_p"
|
jbe@478
|
148 WHERE "supporter"."member_id" = "member_id_v"
|
jbe@478
|
149 AND "issue"."area_id" = "area_id_p"
|
jbe@478
|
150 AND "issue"."state" IN ('admission', 'discussion', 'verification')
|
jbe@478
|
151 AND "self_support"."member_id" ISNULL
|
jbe@478
|
152 AND NOT "initiative_id_ary" @> ARRAY["initiative"."id"]
|
jbe@478
|
153 ORDER BY md5("seed_v" || '-' || "initiative"."id")
|
jbe@478
|
154 LIMIT 1;
|
jbe@478
|
155 IF FOUND THEN
|
jbe@478
|
156 "match_v" := TRUE;
|
jbe@478
|
157 "initiative_id_ary" := "initiative_id_ary" || "result_row"."id";
|
jbe@478
|
158 RETURN NEXT "result_row";
|
jbe@492
|
159 IF array_length("initiative_id_ary", 1) >= "member_row"."notification_sample_size" THEN
|
jbe@478
|
160 RETURN;
|
jbe@478
|
161 END IF;
|
jbe@478
|
162 END IF;
|
jbe@478
|
163 END LOOP;
|
jbe@478
|
164 EXIT WHEN NOT "match_v";
|
jbe@478
|
165 END LOOP;
|
jbe@478
|
166 RETURN;
|
jbe@478
|
167 END;
|
jbe@478
|
168 $$;
|
jbe@478
|
169
|
jbe@478
|
170 CREATE VIEW "updated_or_featured_initiative" AS
|
jbe@478
|
171 SELECT * FROM "updated_initiative"
|
jbe@478
|
172 UNION ALL
|
jbe@478
|
173 SELECT
|
jbe@478
|
174 "member"."id" AS "seen_by_member_id",
|
jbe@478
|
175 FALSE AS "supported",
|
jbe@492
|
176 EXISTS (
|
jbe@492
|
177 SELECT NULL FROM "draft"
|
jbe@492
|
178 WHERE "draft"."initiative_id" = "initiative"."id"
|
jbe@492
|
179 AND COALESCE(
|
jbe@492
|
180 "draft"."id" > "sent"."last_draft_id",
|
jbe@492
|
181 TRUE
|
jbe@492
|
182 )
|
jbe@492
|
183 ) AS "new_draft",
|
jbe@492
|
184 ( SELECT count(1) FROM "suggestion"
|
jbe@492
|
185 WHERE "suggestion"."initiative_id" = "initiative"."id"
|
jbe@492
|
186 AND COALESCE(
|
jbe@492
|
187 "suggestion"."id" > "sent"."last_suggestion_id",
|
jbe@492
|
188 TRUE
|
jbe@492
|
189 )
|
jbe@492
|
190 ) AS "new_suggestion_count",
|
jbe@478
|
191 TRUE AS "featured",
|
jbe@478
|
192 NOT EXISTS (
|
jbe@478
|
193 SELECT NULL FROM "initiative" AS "better_initiative"
|
jbe@478
|
194 WHERE
|
jbe@484
|
195 "better_initiative"."issue_id" = "initiative"."issue_id"
|
jbe@484
|
196 AND
|
jbe@484
|
197 ( COALESCE("better_initiative"."harmonic_weight", -1),
|
jbe@484
|
198 -"better_initiative"."id" ) >
|
jbe@484
|
199 ( COALESCE("initiative"."harmonic_weight", -1),
|
jbe@485
|
200 -"initiative"."id" )
|
jbe@478
|
201 ) AS "leading",
|
jbe@478
|
202 "initiative".*
|
jbe@478
|
203 FROM "member" CROSS JOIN "area"
|
jbe@478
|
204 CROSS JOIN LATERAL
|
jbe@492
|
205 "featured_initiative"("member"."id", "area"."id") AS "initiative"
|
jbe@492
|
206 LEFT JOIN "initiative_notification_sent" AS "sent"
|
jbe@492
|
207 ON "sent"."member_id" = "member"."id"
|
jbe@492
|
208 AND "sent"."initiative_id" = "initiative"."id";
|
jbe@478
|
209
|
jbe@478
|
210 CREATE VIEW "leading_complement_initiative" AS
|
jbe@478
|
211 SELECT * FROM (
|
jbe@478
|
212 SELECT DISTINCT ON ("seen_by_member_id", "initiative"."issue_id")
|
jbe@492
|
213 "uf_initiative"."seen_by_member_id",
|
jbe@492
|
214 "supporter"."member_id" NOTNULL AS "supported",
|
jbe@492
|
215 CASE WHEN "supporter"."member_id" NOTNULL THEN FALSE ELSE
|
jbe@492
|
216 EXISTS (
|
jbe@492
|
217 SELECT NULL FROM "draft"
|
jbe@492
|
218 WHERE "draft"."initiative_id" = "initiative"."id"
|
jbe@492
|
219 AND COALESCE(
|
jbe@492
|
220 "draft"."id" > "sent"."last_draft_id",
|
jbe@492
|
221 TRUE
|
jbe@492
|
222 )
|
jbe@492
|
223 )
|
jbe@492
|
224 END AS "new_draft",
|
jbe@492
|
225 CASE WHEN "supporter"."member_id" NOTNULL THEN 0 ELSE
|
jbe@492
|
226 ( SELECT count(1) FROM "suggestion"
|
jbe@492
|
227 WHERE "suggestion"."initiative_id" = "initiative"."id"
|
jbe@492
|
228 AND COALESCE(
|
jbe@492
|
229 "suggestion"."id" > "sent"."last_suggestion_id",
|
jbe@492
|
230 TRUE
|
jbe@492
|
231 )
|
jbe@492
|
232 )
|
jbe@492
|
233 END AS "new_suggestion_count",
|
jbe@478
|
234 FALSE AS "featured",
|
jbe@478
|
235 TRUE AS "leading",
|
jbe@478
|
236 "initiative".*
|
jbe@492
|
237 FROM "updated_or_featured_initiative" AS "uf_initiative"
|
jbe@492
|
238 JOIN "initiative" ON
|
jbe@492
|
239 "uf_initiative"."issue_id" = "initiative"."issue_id"
|
jbe@492
|
240 LEFT JOIN "supporter" ON
|
jbe@492
|
241 "supporter"."member_id" = "uf_initiative"."seen_by_member_id" AND
|
jbe@492
|
242 "supporter"."initiative_id" = "initiative"."id"
|
jbe@492
|
243 LEFT JOIN "initiative_notification_sent" AS "sent"
|
jbe@492
|
244 ON "sent"."member_id" = "uf_initiative"."seen_by_member_id"
|
jbe@492
|
245 AND "sent"."initiative_id" = "initiative"."id"
|
jbe@478
|
246 ORDER BY
|
jbe@478
|
247 "seen_by_member_id",
|
jbe@478
|
248 "initiative"."issue_id",
|
jbe@478
|
249 "initiative"."harmonic_weight" DESC,
|
jbe@478
|
250 "initiative"."id"
|
jbe@478
|
251 ) AS "subquery"
|
jbe@478
|
252 WHERE NOT EXISTS (
|
jbe@478
|
253 SELECT NULL FROM "updated_or_featured_initiative" AS "other"
|
jbe@478
|
254 WHERE "other"."seen_by_member_id" = "subquery"."seen_by_member_id"
|
jbe@478
|
255 AND "other"."id" = "subquery"."id"
|
jbe@478
|
256 );
|
jbe@478
|
257
|
jbe@492
|
258 CREATE VIEW "unfiltered_initiative_for_notification" AS
|
jbe@478
|
259 SELECT * FROM "updated_or_featured_initiative"
|
jbe@478
|
260 UNION ALL
|
jbe@478
|
261 SELECT * FROM "leading_complement_initiative";
|
jbe@478
|
262
|
jbe@492
|
263 CREATE VIEW "initiative_for_notification" AS
|
jbe@492
|
264 SELECT "initiative1".*
|
jbe@492
|
265 FROM "unfiltered_initiative_for_notification" "initiative1"
|
jbe@492
|
266 JOIN "issue" AS "issue1" ON "initiative1"."issue_id" = "issue1"."id"
|
jbe@492
|
267 WHERE EXISTS (
|
jbe@492
|
268 SELECT NULL
|
jbe@492
|
269 FROM "unfiltered_initiative_for_notification" "initiative2"
|
jbe@492
|
270 JOIN "issue" AS "issue2" ON "initiative2"."issue_id" = "issue2"."id"
|
jbe@492
|
271 WHERE "initiative1"."seen_by_member_id" = "initiative2"."seen_by_member_id"
|
jbe@492
|
272 AND "issue1"."area_id" = "issue2"."area_id"
|
jbe@492
|
273 AND ( "initiative2"."new_draft" OR "initiative2"."new_suggestion_count" > 0 )
|
jbe@492
|
274 );
|
jbe@492
|
275
|
jbe@497
|
276 CREATE VIEW "newsletter_to_send" AS
|
jbe@497
|
277 SELECT
|
jbe@497
|
278 "newsletter"."id" AS "newsletter_id",
|
jbe@497
|
279 "member"."id" AS "member_id"
|
jbe@497
|
280 FROM "newsletter" CROSS JOIN "member"
|
jbe@497
|
281 LEFT JOIN "privilege" ON
|
jbe@497
|
282 "privilege"."member_id" = "member"."id" AND
|
jbe@497
|
283 "privilege"."unit_id" = "newsletter"."unit_id" AND
|
jbe@497
|
284 "privilege"."voting_right" = TRUE
|
jbe@497
|
285 LEFT JOIN "subscription" ON
|
jbe@497
|
286 "subscription"."member_id" = "member"."id" AND
|
jbe@497
|
287 "subscription"."unit_id" = "newsletter"."unit_id"
|
jbe@497
|
288 WHERE "newsletter"."published" >= now()
|
jbe@497
|
289 AND "newsletter"."sent" ISNULL
|
jbe@497
|
290 AND "member"."locked" = FALSE
|
jbe@497
|
291 AND (
|
jbe@497
|
292 "member"."disable_notifications" = FALSE OR
|
jbe@497
|
293 "newsletter"."include_all_members" = TRUE )
|
jbe@497
|
294 AND (
|
jbe@497
|
295 "newsletter"."unit_id" ISNULL OR
|
jbe@497
|
296 "privilege"."member_id" NOTNULL OR
|
jbe@497
|
297 "subscription"."member_id" NOTNULL );
|
jbe@497
|
298
|
jbe@492
|
299 CREATE FUNCTION "get_initiatives_for_notification"
|
jbe@492
|
300 ( "member_id_p" "member"."id"%TYPE )
|
jbe@492
|
301 RETURNS SETOF "initiative_for_notification"
|
jbe@492
|
302 LANGUAGE 'plpgsql' VOLATILE AS $$
|
jbe@492
|
303 DECLARE
|
jbe@492
|
304 "result_row" "initiative_for_notification"%ROWTYPE;
|
jbe@492
|
305 "last_draft_id_v" "draft"."id"%TYPE;
|
jbe@492
|
306 "last_suggestion_id_v" "suggestion"."id"%TYPE;
|
jbe@492
|
307 BEGIN
|
jbe@492
|
308 PERFORM "require_transaction_isolation"();
|
jbe@492
|
309 PERFORM NULL FROM "member" WHERE "id" = "member_id_p" FOR UPDATE;
|
jbe@492
|
310 FOR "result_row" IN
|
jbe@492
|
311 SELECT * FROM "initiative_for_notification"
|
jbe@492
|
312 WHERE "seen_by_member_id" = "member_id_p"
|
jbe@492
|
313 LOOP
|
jbe@492
|
314 SELECT "id" INTO "last_draft_id_v" FROM "draft"
|
jbe@492
|
315 WHERE "draft"."initiative_id" = "result_row"."id"
|
jbe@492
|
316 ORDER BY "id" DESC LIMIT 1;
|
jbe@492
|
317 SELECT "id" INTO "last_suggestion_id_v" FROM "suggestion"
|
jbe@492
|
318 WHERE "suggestion"."initiative_id" = "result_row"."id"
|
jbe@492
|
319 ORDER BY "id" DESC LIMIT 1;
|
jbe@492
|
320 INSERT INTO "initiative_notification_sent"
|
jbe@492
|
321 ("member_id", "initiative_id", "last_draft_id", "last_suggestion_id")
|
jbe@492
|
322 VALUES (
|
jbe@492
|
323 "member_id_p",
|
jbe@492
|
324 "result_row"."id",
|
jbe@493
|
325 "last_draft_id_v",
|
jbe@493
|
326 "last_suggestion_id_v" )
|
jbe@492
|
327 ON CONFLICT ("member_id", "initiative_id") DO UPDATE SET
|
jbe@492
|
328 "last_draft_id" = CASE
|
jbe@494
|
329 WHEN "initiative_notification_sent"."last_draft_id" > "last_draft_id_v"
|
jbe@494
|
330 THEN "initiative_notification_sent"."last_draft_id"
|
jbe@492
|
331 ELSE "last_draft_id_v"
|
jbe@492
|
332 END,
|
jbe@492
|
333 "last_suggestion_id" = CASE
|
jbe@494
|
334 WHEN "initiative_notification_sent"."last_suggestion_id" > "last_suggestion_id_v"
|
jbe@494
|
335 THEN "initiative_notification_sent"."last_suggestion_id"
|
jbe@492
|
336 ELSE "last_suggestion_id_v"
|
jbe@492
|
337 END;
|
jbe@492
|
338 RETURN NEXT "result_row";
|
jbe@492
|
339 END LOOP;
|
jbe@492
|
340 DELETE FROM "initiative_notification_sent"
|
jbe@492
|
341 USING "initiative", "issue"
|
jbe@492
|
342 WHERE "initiative_notification_sent"."member_id" = "member_id_p"
|
jbe@492
|
343 AND "initiative"."id" = "initiative_notification_sent"."initiative_id"
|
jbe@492
|
344 AND "issue"."id" = "initiative"."issue_id"
|
jbe@492
|
345 AND ( "issue"."closed" NOTNULL OR "issue"."fully_frozen" NOTNULL );
|
jbe@492
|
346 UPDATE "member" SET "notification_counter" = "notification_counter" + 1
|
jbe@492
|
347 WHERE "id" = "member_id_p";
|
jbe@492
|
348 RETURN;
|
jbe@492
|
349 END;
|
jbe@492
|
350 $$;
|
jbe@492
|
351
|
jbe@478
|
352 COMMIT;
|