| rev |
line source |
|
jbe@517
|
1 BEGIN;
|
|
jbe@517
|
2
|
|
jbe@517
|
3 CREATE OR REPLACE VIEW "liquid_feedback_version" AS
|
|
jbe@517
|
4 SELECT * FROM (VALUES ('3.2.1', 3, 2, 1))
|
|
jbe@517
|
5 AS "subquery"("string", "major", "minor", "revision");
|
|
jbe@517
|
6
|
|
jbe@517
|
7 CREATE OR REPLACE FUNCTION "get_initiatives_for_notification"
|
|
jbe@517
|
8 ( "recipient_id_p" "member"."id"%TYPE )
|
|
jbe@517
|
9 RETURNS SETOF "initiative_for_notification"
|
|
jbe@517
|
10 LANGUAGE 'plpgsql' VOLATILE AS $$
|
|
jbe@517
|
11 DECLARE
|
|
jbe@517
|
12 "result_row" "initiative_for_notification"%ROWTYPE;
|
|
jbe@517
|
13 "last_draft_id_v" "draft"."id"%TYPE;
|
|
jbe@517
|
14 "last_suggestion_id_v" "suggestion"."id"%TYPE;
|
|
jbe@517
|
15 BEGIN
|
|
jbe@517
|
16 PERFORM "require_transaction_isolation"();
|
|
jbe@517
|
17 PERFORM NULL FROM "member" WHERE "id" = "recipient_id_p" FOR UPDATE;
|
|
jbe@517
|
18 FOR "result_row" IN
|
|
jbe@517
|
19 SELECT * FROM "initiative_for_notification"
|
|
jbe@517
|
20 WHERE "recipient_id" = "recipient_id_p"
|
|
jbe@517
|
21 LOOP
|
|
jbe@517
|
22 SELECT "id" INTO "last_draft_id_v" FROM "draft"
|
|
jbe@517
|
23 WHERE "draft"."initiative_id" = "result_row"."initiative_id"
|
|
jbe@517
|
24 ORDER BY "id" DESC LIMIT 1;
|
|
jbe@517
|
25 SELECT "id" INTO "last_suggestion_id_v" FROM "suggestion"
|
|
jbe@517
|
26 WHERE "suggestion"."initiative_id" = "result_row"."initiative_id"
|
|
jbe@517
|
27 ORDER BY "id" DESC LIMIT 1;
|
|
jbe@517
|
28 /* compatibility with PostgreSQL 9.1 */
|
|
jbe@517
|
29 DELETE FROM "notification_initiative_sent"
|
|
jbe@517
|
30 WHERE "member_id" = "recipient_id_p"
|
|
jbe@517
|
31 AND "initiative_id" = "result_row"."initiative_id";
|
|
jbe@517
|
32 INSERT INTO "notification_initiative_sent"
|
|
jbe@517
|
33 ("member_id", "initiative_id", "last_draft_id", "last_suggestion_id")
|
|
jbe@517
|
34 VALUES (
|
|
jbe@517
|
35 "recipient_id_p",
|
|
jbe@517
|
36 "result_row"."initiative_id",
|
|
jbe@517
|
37 "last_draft_id_v",
|
|
jbe@517
|
38 "last_suggestion_id_v" );
|
|
jbe@517
|
39 /* TODO: use alternative code below, requires PostgreSQL 9.5 or higher
|
|
jbe@517
|
40 INSERT INTO "notification_initiative_sent"
|
|
jbe@517
|
41 ("member_id", "initiative_id", "last_draft_id", "last_suggestion_id")
|
|
jbe@517
|
42 VALUES (
|
|
jbe@517
|
43 "recipient_id_p",
|
|
jbe@517
|
44 "result_row"."initiative_id",
|
|
jbe@517
|
45 "last_draft_id_v",
|
|
jbe@517
|
46 "last_suggestion_id_v" )
|
|
jbe@517
|
47 ON CONFLICT ("member_id", "initiative_id") DO UPDATE SET
|
|
jbe@517
|
48 "last_draft_id" = "last_draft_id_v",
|
|
jbe@517
|
49 "last_suggestion_id" = "last_suggestion_id_v";
|
|
jbe@517
|
50 */
|
|
jbe@517
|
51 RETURN NEXT "result_row";
|
|
jbe@517
|
52 END LOOP;
|
|
jbe@517
|
53 DELETE FROM "notification_initiative_sent"
|
|
jbe@517
|
54 USING "initiative", "issue"
|
|
jbe@517
|
55 WHERE "notification_initiative_sent"."member_id" = "recipient_id_p"
|
|
jbe@517
|
56 AND "initiative"."id" = "notification_initiative_sent"."initiative_id"
|
|
jbe@517
|
57 AND "issue"."id" = "initiative"."issue_id"
|
|
jbe@517
|
58 AND ( "issue"."closed" NOTNULL OR "issue"."fully_frozen" NOTNULL );
|
|
jbe@517
|
59 UPDATE "member" SET
|
|
jbe@517
|
60 "notification_counter" = "notification_counter" + 1,
|
|
jbe@517
|
61 "notification_sent" = now()
|
|
jbe@517
|
62 WHERE "id" = "recipient_id_p";
|
|
jbe@517
|
63 RETURN;
|
|
jbe@517
|
64 END;
|
|
jbe@517
|
65 $$;
|
|
jbe@517
|
66
|
|
jbe@517
|
67 COMMIT;
|