From c3ab29e68cb062048266bd5a035bb68faa8d2960 Mon Sep 17 00:00:00 2001
From: Moritz Bunkus <m.bunkus@linet-services.de>
Date: Wed, 3 Dec 2014 12:25:57 +0100
Subject: [PATCH] =?utf8?q?Benutzerdefinierte=20Variablen=20via=20Trigger?=
 =?utf8?q?=20l=C3=B6schen=20Bugfix=202?=
MIME-Version: 1.0
Content-Type: text/plain; charset=utf8
Content-Transfer-Encoding: 8bit

Die Triggerfunktion selber sollte aus Performancegründen nicht »DELETE …
WHERE … IN (SELECT…)« nutzen, da dies bei PostgreSQL sehr langsam ist.
---
 .../delete_cvars_on_trans_deletion_fix2.sql      | 16 ++++++++++++++++
 1 file changed, 16 insertions(+)
 create mode 100644 sql/Pg-upgrade2/delete_cvars_on_trans_deletion_fix2.sql

diff --git a/sql/Pg-upgrade2/delete_cvars_on_trans_deletion_fix2.sql b/sql/Pg-upgrade2/delete_cvars_on_trans_deletion_fix2.sql
new file mode 100644
index 000000000..b0e5935ad
--- /dev/null
+++ b/sql/Pg-upgrade2/delete_cvars_on_trans_deletion_fix2.sql
@@ -0,0 +1,16 @@
+-- @tag: delete_cvars_on_trans_deletion_fix2
+-- @description: Bugfix 2 für das Löschen von benutzerdefinierten Variablen via Triggerfunktionen
+-- @depends: delete_cvars_on_trans_deletion_fix1
+
+-- 2.1. Parametrisierte Backend-Funktion zum Löschen:
+CREATE OR REPLACE FUNCTION delete_custom_variables_with_sub_module(config_module TEXT, cvar_sub_module TEXT, old_id INTEGER)
+RETURNS BOOLEAN AS $$
+  BEGIN
+    DELETE FROM custom_variables
+    WHERE EXISTS (SELECT id FROM custom_variable_configs cfg WHERE (cfg.module = config_module) AND (custom_variables.config_id = cfg.id))
+      AND (COALESCE(sub_module, '') = cvar_sub_module)
+      AND (trans_id                 = old_id);
+
+    RETURN TRUE;
+  END;
+$$ LANGUAGE plpgsql;
-- 
2.20.1