1 BEGIN;

2

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");

6

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 $$;

66

67 COMMIT;