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