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