liquid_feedback_core
diff update/core-update.v3.2.0-v3.2.1.sql @ 517:e4f619e87664
Fixed accidental PostgreSQL 9.5 dependency (because of UPSERT aka ON CONFLICT clause)
author | jbe |
---|---|
date | Sat Apr 30 17:03:48 2016 +0200 (2016-04-30) |
parents | |
children |
line diff
1.1 --- /dev/null Thu Jan 01 00:00:00 1970 +0000 1.2 +++ b/update/core-update.v3.2.0-v3.2.1.sql Sat Apr 30 17:03:48 2016 +0200 1.3 @@ -0,0 +1,67 @@ 1.4 +BEGIN; 1.5 + 1.6 +CREATE OR REPLACE VIEW "liquid_feedback_version" AS 1.7 + SELECT * FROM (VALUES ('3.2.1', 3, 2, 1)) 1.8 + AS "subquery"("string", "major", "minor", "revision"); 1.9 + 1.10 +CREATE OR REPLACE FUNCTION "get_initiatives_for_notification" 1.11 + ( "recipient_id_p" "member"."id"%TYPE ) 1.12 + RETURNS SETOF "initiative_for_notification" 1.13 + LANGUAGE 'plpgsql' VOLATILE AS $$ 1.14 + DECLARE 1.15 + "result_row" "initiative_for_notification"%ROWTYPE; 1.16 + "last_draft_id_v" "draft"."id"%TYPE; 1.17 + "last_suggestion_id_v" "suggestion"."id"%TYPE; 1.18 + BEGIN 1.19 + PERFORM "require_transaction_isolation"(); 1.20 + PERFORM NULL FROM "member" WHERE "id" = "recipient_id_p" FOR UPDATE; 1.21 + FOR "result_row" IN 1.22 + SELECT * FROM "initiative_for_notification" 1.23 + WHERE "recipient_id" = "recipient_id_p" 1.24 + LOOP 1.25 + SELECT "id" INTO "last_draft_id_v" FROM "draft" 1.26 + WHERE "draft"."initiative_id" = "result_row"."initiative_id" 1.27 + ORDER BY "id" DESC LIMIT 1; 1.28 + SELECT "id" INTO "last_suggestion_id_v" FROM "suggestion" 1.29 + WHERE "suggestion"."initiative_id" = "result_row"."initiative_id" 1.30 + ORDER BY "id" DESC LIMIT 1; 1.31 + /* compatibility with PostgreSQL 9.1 */ 1.32 + DELETE FROM "notification_initiative_sent" 1.33 + WHERE "member_id" = "recipient_id_p" 1.34 + AND "initiative_id" = "result_row"."initiative_id"; 1.35 + INSERT INTO "notification_initiative_sent" 1.36 + ("member_id", "initiative_id", "last_draft_id", "last_suggestion_id") 1.37 + VALUES ( 1.38 + "recipient_id_p", 1.39 + "result_row"."initiative_id", 1.40 + "last_draft_id_v", 1.41 + "last_suggestion_id_v" ); 1.42 + /* TODO: use alternative code below, requires PostgreSQL 9.5 or higher 1.43 + INSERT INTO "notification_initiative_sent" 1.44 + ("member_id", "initiative_id", "last_draft_id", "last_suggestion_id") 1.45 + VALUES ( 1.46 + "recipient_id_p", 1.47 + "result_row"."initiative_id", 1.48 + "last_draft_id_v", 1.49 + "last_suggestion_id_v" ) 1.50 + ON CONFLICT ("member_id", "initiative_id") DO UPDATE SET 1.51 + "last_draft_id" = "last_draft_id_v", 1.52 + "last_suggestion_id" = "last_suggestion_id_v"; 1.53 + */ 1.54 + RETURN NEXT "result_row"; 1.55 + END LOOP; 1.56 + DELETE FROM "notification_initiative_sent" 1.57 + USING "initiative", "issue" 1.58 + WHERE "notification_initiative_sent"."member_id" = "recipient_id_p" 1.59 + AND "initiative"."id" = "notification_initiative_sent"."initiative_id" 1.60 + AND "issue"."id" = "initiative"."issue_id" 1.61 + AND ( "issue"."closed" NOTNULL OR "issue"."fully_frozen" NOTNULL ); 1.62 + UPDATE "member" SET 1.63 + "notification_counter" = "notification_counter" + 1, 1.64 + "notification_sent" = now() 1.65 + WHERE "id" = "recipient_id_p"; 1.66 + RETURN; 1.67 + END; 1.68 + $$; 1.69 + 1.70 +COMMIT;