1 -- @tag: convert_curr_to_text
2 -- @description: Spalte 'curr' von 'char(3)' nach 'text' konvertieren
3 -- @depends: release_2_7_0
6 -- Zuerst alle Spaltentypen konvertieren.
7 ALTER TABLE ap ALTER COLUMN curr TYPE text;
8 ALTER TABLE ar ALTER COLUMN curr TYPE text;
9 ALTER TABLE customer ALTER COLUMN curr TYPE text;
10 ALTER TABLE delivery_orders ALTER COLUMN curr TYPE text;
11 ALTER TABLE exchangerate ALTER COLUMN curr TYPE text;
12 ALTER TABLE rma ALTER COLUMN curr TYPE text;
13 ALTER TABLE vendor ALTER COLUMN curr TYPE text;
15 -- Eventuell falsche Inhalte (Leerzeichenpadding) auf leere Strings setzen.
16 UPDATE ap SET curr = '' WHERE (curr SIMILAR TO '^ +$') OR (curr IS NULL);
17 UPDATE ar SET curr = '' WHERE (curr SIMILAR TO '^ +$') OR (curr IS NULL);
18 UPDATE customer SET curr = '' WHERE (curr SIMILAR TO '^ +$') OR (curr IS NULL);
19 UPDATE delivery_orders SET curr = '' WHERE (curr SIMILAR TO '^ +$') OR (curr IS NULL);
20 UPDATE exchangerate SET curr = '' WHERE (curr SIMILAR TO '^ +$') OR (curr IS NULL);
21 UPDATE oe SET curr = '' WHERE (curr SIMILAR TO '^ +$') OR (curr IS NULL);
22 UPDATE rma SET curr = '' WHERE (curr SIMILAR TO '^ +$') OR (curr IS NULL);
23 UPDATE vendor SET curr = '' WHERE (curr SIMILAR TO '^ +$') OR (curr IS NULL);
25 -- Nun noch die stored procedures anpassen.
26 CREATE OR REPLACE FUNCTION del_exchangerate() RETURNS trigger
35 SELECT INTO d_curr substring(curr FROM '[^:]*') FROM DEFAULTS;
37 IF TG_RELNAME = 'ar' THEN
38 SELECT INTO t_curr, t_transdate curr, transdate FROM ar WHERE id = old.id;
41 IF TG_RELNAME = 'ap' THEN
42 SELECT INTO t_curr, t_transdate curr, transdate FROM ap WHERE id = old.id;
45 IF TG_RELNAME = 'oe' THEN
46 SELECT INTO t_curr, t_transdate curr, transdate FROM oe WHERE id = old.id;
49 IF TG_RELNAME = 'delivery_orders' THEN
50 SELECT INTO t_curr, t_transdate curr, transdate FROM delivery_orders WHERE id = old.id;
53 IF d_curr != t_curr THEN
54 SELECT INTO t_id a.id FROM acc_trans ac
55 JOIN ar a ON (a.id = ac.trans_id)
56 WHERE (a.curr = t_curr)
57 AND (ac.transdate = t_transdate)
66 JOIN ap a ON (a.id = ac.trans_id)
67 WHERE (a.curr = t_curr)
68 AND (ac.transdate = t_transdate)
77 WHERE (o.curr = t_curr)
78 AND (o.transdate = t_transdate)
86 FROM delivery_orders dord
87 WHERE (dord.curr = t_curr)
88 AND (dord.transdate = t_transdate)
90 FROM delivery_orders dord
91 WHERE (dord.id = old.id);
94 DELETE FROM exchangerate
96 AND (transdate = t_transdate);
104 -- Und die stored procedure auch auf delivery_orders anwenden
105 CREATE TRIGGER del_exchangerate
106 BEFORE DELETE ON delivery_orders
108 EXECUTE PROCEDURE del_exchangerate();