# HG changeset patch
# User jbe
# Date 1536064241 -7200
# Node ID 225a0c0476919d13d5ab93eee6222680f09fd975
# Parent  1cb6710fc4296baf56ddcc5135722a522a3cee5c
Bugfix in function "write_event_initiative_revoked_trigger" (wrong use of SELECT, needs to be PERFORM)

diff -r 1cb6710fc429 -r 225a0c047691 core.sql
--- a/core.sql	Sun Mar 04 18:10:07 2018 +0100
+++ b/core.sql	Tue Sep 04 14:30:41 2018 +0200
@@ -1999,13 +1999,13 @@
     BEGIN
       IF OLD."revoked" ISNULL AND NEW."revoked" NOTNULL THEN
         -- NOTE: lock for primary key update to avoid new drafts
-        SELECT NULL FROM "initiative" WHERE "id" = NEW."id" FOR UPDATE;
+        PERFORM NULL FROM "initiative" WHERE "id" = NEW."id" FOR UPDATE;
         SELECT * INTO "issue_row" FROM "issue"
           WHERE "id" = NEW."issue_id" FOR SHARE;
         SELECT * INTO "area_row" FROM "area"
           WHERE "id" = "issue_row"."area_id" FOR SHARE;
         -- NOTE: FOR SHARE cannot be used with DISTINCT in view "current_draft"
-        SELECT NULL FROM "draft" WHERE "initiative_id" = NEW."id" FOR SHARE;
+        PERFORM NULL FROM "draft" WHERE "initiative_id" = NEW."id" FOR SHARE;
         SELECT "id" INTO "draft_id_v" FROM "current_draft"
           WHERE "initiative_id" = NEW."id";
         INSERT INTO "event" (
diff -r 1cb6710fc429 -r 225a0c047691 update/core-update.v4.0.0-v4.0.1.sql
--- a/update/core-update.v4.0.0-v4.0.1.sql	Sun Mar 04 18:10:07 2018 +0100
+++ b/update/core-update.v4.0.0-v4.0.1.sql	Tue Sep 04 14:30:41 2018 +0200
@@ -66,4 +66,39 @@
 COMMENT ON COLUMN "system_application"."base_url"     IS 'Base URL for users';
 COMMENT ON COLUMN "system_application"."manifest_url" IS 'URL referring to a manifest that can be used for application (type/version) discovery';
 
+CREATE OR REPLACE FUNCTION "write_event_initiative_revoked_trigger"()
+  RETURNS TRIGGER
+  LANGUAGE 'plpgsql' VOLATILE AS $$
+    DECLARE
+      "issue_row"  "issue"%ROWTYPE;
+      "area_row"   "area"%ROWTYPE;
+      "draft_id_v" "draft"."id"%TYPE;
+    BEGIN
+      IF OLD."revoked" ISNULL AND NEW."revoked" NOTNULL THEN
+        -- NOTE: lock for primary key update to avoid new drafts
+        PERFORM NULL FROM "initiative" WHERE "id" = NEW."id" FOR UPDATE;
+        SELECT * INTO "issue_row" FROM "issue"
+          WHERE "id" = NEW."issue_id" FOR SHARE;
+        SELECT * INTO "area_row" FROM "area"
+          WHERE "id" = "issue_row"."area_id" FOR SHARE;
+        -- NOTE: FOR SHARE cannot be used with DISTINCT in view "current_draft"
+        PERFORM NULL FROM "draft" WHERE "initiative_id" = NEW."id" FOR SHARE;
+        SELECT "id" INTO "draft_id_v" FROM "current_draft"
+          WHERE "initiative_id" = NEW."id";
+        INSERT INTO "event" (
+            "event", "member_id",
+            "unit_id", "area_id", "policy_id", "issue_id", "state",
+            "initiative_id", "draft_id"
+          ) VALUES (
+            'initiative_revoked', NEW."revoked_by_member_id",
+            "area_row"."unit_id", "issue_row"."area_id",
+            "issue_row"."policy_id",
+            NEW."issue_id", "issue_row"."state",
+            NEW."id", "draft_id_v"
+          );
+      END IF;
+      RETURN NULL;
+    END;
+  $$;
+
 COMMIT;