Währungen mit mehr als drei Zeichen erlauben & Spaltentyp 'text' anstelle von 'char(3)'

[mfinanz.git] / sql / Pg-upgrade2 / convert_curr_to_text.sql

diff --git a/sql/Pg-upgrade2/convert_curr_to_text.sql b/sql/Pg-upgrade2/convert_curr_to_text.sql
new file mode 100644 (file)
index 0000000..90cc034
--- /dev/null
+++ b/sql/Pg-upgrade2/convert_curr_to_text.sql
@@ -0,0 +1,108 @@
+-- @tag: convert_curr_to_text
+-- @description: Spalte 'curr' von 'char(3)' nach 'text' konvertieren
+-- @depends: release_2_7_0
+-- @charset: utf-8
+
+-- Zuerst alle Spaltentypen konvertieren.
+ALTER TABLE ap              ALTER COLUMN curr TYPE text;
+ALTER TABLE ar              ALTER COLUMN curr TYPE text;
+ALTER TABLE customer        ALTER COLUMN curr TYPE text;
+ALTER TABLE delivery_orders ALTER COLUMN curr TYPE text;
+ALTER TABLE exchangerate    ALTER COLUMN curr TYPE text;
+ALTER TABLE rma             ALTER COLUMN curr TYPE text;
+ALTER TABLE vendor          ALTER COLUMN curr TYPE text;
+
+-- Eventuell falsche Inhalte (Leerzeichenpadding) auf leere Strings setzen.
+UPDATE ap              SET curr = '' WHERE (curr SIMILAR TO '^ +$') OR (curr IS NULL);
+UPDATE ar              SET curr = '' WHERE (curr SIMILAR TO '^ +$') OR (curr IS NULL);
+UPDATE customer        SET curr = '' WHERE (curr SIMILAR TO '^ +$') OR (curr IS NULL);
+UPDATE delivery_orders SET curr = '' WHERE (curr SIMILAR TO '^ +$') OR (curr IS NULL);
+UPDATE exchangerate    SET curr = '' WHERE (curr SIMILAR TO '^ +$') OR (curr IS NULL);
+UPDATE oe              SET curr = '' WHERE (curr SIMILAR TO '^ +$') OR (curr IS NULL);
+UPDATE rma             SET curr = '' WHERE (curr SIMILAR TO '^ +$') OR (curr IS NULL);
+UPDATE vendor          SET curr = '' WHERE (curr SIMILAR TO '^ +$') OR (curr IS NULL);
+
+-- Nun noch die stored procedures anpassen.
+CREATE OR REPLACE FUNCTION del_exchangerate() RETURNS trigger
+  LANGUAGE plpgsql
+  AS $$
+    DECLARE
+      t_transdate date;
+      t_curr      text;
+      t_id        int;
+      d_curr      text;
+    BEGIN
+      SELECT INTO d_curr substring(curr FROM '[^:]*') FROM DEFAULTS;
+
+      IF TG_RELNAME = 'ar' THEN
+        SELECT INTO t_curr, t_transdate curr, transdate FROM ar WHERE id = old.id;
+      END IF;
+
+      IF TG_RELNAME = 'ap' THEN
+        SELECT INTO t_curr, t_transdate curr, transdate FROM ap WHERE id = old.id;
+      END IF;
+
+      IF TG_RELNAME = 'oe' THEN
+        SELECT INTO t_curr, t_transdate curr, transdate FROM oe WHERE id = old.id;
+      END IF;
+
+      IF TG_RELNAME = 'delivery_orders' THEN
+        SELECT INTO t_curr, t_transdate curr, transdate FROM delivery_orders WHERE id = old.id;
+      END IF;
+
+      IF d_curr != t_curr THEN
+        SELECT INTO t_id a.id FROM acc_trans ac
+          JOIN ar a ON (a.id = ac.trans_id)
+          WHERE (a.curr       = t_curr)
+            AND (ac.transdate = t_transdate)
+        EXCEPT SELECT a.id
+          FROM ar a
+          WHERE (a.id = old.id)
+
+        UNION
+
+        SELECT a.id
+          FROM acc_trans ac
+          JOIN ap a ON (a.id = ac.trans_id)
+          WHERE (a.curr       = t_curr)
+            AND (ac.transdate = t_transdate)
+        EXCEPT SELECT a.id
+          FROM ap a
+          WHERE (a.id = old.id)
+
+        UNION
+
+        SELECT o.id
+          FROM oe o
+          WHERE (o.curr      = t_curr)
+            AND (o.transdate = t_transdate)
+        EXCEPT SELECT o.id
+        FROM oe o
+        WHERE (o.id = old.id)
+
+        UNION
+
+        SELECT dord.id
+          FROM delivery_orders dord
+          WHERE (dord.curr      = t_curr)
+            AND (dord.transdate = t_transdate)
+        EXCEPT SELECT dord.id
+        FROM delivery_orders dord
+        WHERE (dord.id = old.id);
+
+        IF NOT FOUND THEN
+          DELETE FROM exchangerate
+          WHERE (curr      = t_curr)
+            AND (transdate = t_transdate);
+        END IF;
+      END IF;
+
+      RETURN old;
+    END;
+$$;
+
+-- Und die stored procedure auch auf delivery_orders anwenden
+CREATE TRIGGER del_exchangerate
+    BEFORE DELETE ON delivery_orders
+    FOR EACH ROW
+    EXECUTE PROCEDURE del_exchangerate();