jbe@517: BEGIN;
jbe@517: 
jbe@517: CREATE OR REPLACE VIEW "liquid_feedback_version" AS
jbe@517:   SELECT * FROM (VALUES ('3.2.1', 3, 2, 1))
jbe@517:   AS "subquery"("string", "major", "minor", "revision");
jbe@517: 
jbe@517: CREATE OR REPLACE FUNCTION "get_initiatives_for_notification"
jbe@517:   ( "recipient_id_p" "member"."id"%TYPE )
jbe@517:   RETURNS SETOF "initiative_for_notification"
jbe@517:   LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@517:     DECLARE
jbe@517:       "result_row"           "initiative_for_notification"%ROWTYPE;
jbe@517:       "last_draft_id_v"      "draft"."id"%TYPE;
jbe@517:       "last_suggestion_id_v" "suggestion"."id"%TYPE;
jbe@517:     BEGIN
jbe@517:       PERFORM "require_transaction_isolation"();
jbe@517:       PERFORM NULL FROM "member" WHERE "id" = "recipient_id_p" FOR UPDATE;
jbe@517:       FOR "result_row" IN
jbe@517:         SELECT * FROM "initiative_for_notification"
jbe@517:         WHERE "recipient_id" = "recipient_id_p"
jbe@517:       LOOP
jbe@517:         SELECT "id" INTO "last_draft_id_v" FROM "draft"
jbe@517:           WHERE "draft"."initiative_id" = "result_row"."initiative_id"
jbe@517:           ORDER BY "id" DESC LIMIT 1;
jbe@517:         SELECT "id" INTO "last_suggestion_id_v" FROM "suggestion"
jbe@517:           WHERE "suggestion"."initiative_id" = "result_row"."initiative_id"
jbe@517:           ORDER BY "id" DESC LIMIT 1;
jbe@517:         /* compatibility with PostgreSQL 9.1 */
jbe@517:         DELETE FROM "notification_initiative_sent"
jbe@517:           WHERE "member_id" = "recipient_id_p"
jbe@517:           AND "initiative_id" = "result_row"."initiative_id";
jbe@517:         INSERT INTO "notification_initiative_sent"
jbe@517:           ("member_id", "initiative_id", "last_draft_id", "last_suggestion_id")
jbe@517:           VALUES (
jbe@517:             "recipient_id_p",
jbe@517:             "result_row"."initiative_id",
jbe@517:             "last_draft_id_v",
jbe@517:             "last_suggestion_id_v" );
jbe@517:         /* TODO: use alternative code below, requires PostgreSQL 9.5 or higher
jbe@517:         INSERT INTO "notification_initiative_sent"
jbe@517:           ("member_id", "initiative_id", "last_draft_id", "last_suggestion_id")
jbe@517:           VALUES (
jbe@517:             "recipient_id_p",
jbe@517:             "result_row"."initiative_id",
jbe@517:             "last_draft_id_v",
jbe@517:             "last_suggestion_id_v" )
jbe@517:           ON CONFLICT ("member_id", "initiative_id") DO UPDATE SET
jbe@517:             "last_draft_id" = "last_draft_id_v",
jbe@517:             "last_suggestion_id" = "last_suggestion_id_v";
jbe@517:         */
jbe@517:         RETURN NEXT "result_row";
jbe@517:       END LOOP;
jbe@517:       DELETE FROM "notification_initiative_sent"
jbe@517:         USING "initiative", "issue"
jbe@517:         WHERE "notification_initiative_sent"."member_id" = "recipient_id_p"
jbe@517:         AND "initiative"."id" = "notification_initiative_sent"."initiative_id"
jbe@517:         AND "issue"."id" = "initiative"."issue_id"
jbe@517:         AND ( "issue"."closed" NOTNULL OR "issue"."fully_frozen" NOTNULL );
jbe@517:       UPDATE "member" SET
jbe@517:         "notification_counter" = "notification_counter" + 1,
jbe@517:         "notification_sent" = now()
jbe@517:         WHERE "id" = "recipient_id_p";
jbe@517:       RETURN;
jbe@517:     END;
jbe@517:   $$;
jbe@517: 
jbe@517: COMMIT;