+-- @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();
projects / mfinanz.git / commitdiff