1 -- @tag: part_type_enum
3 -- @depends: release_3_4_1
5 CREATE TYPE part_type_enum AS ENUM ('part', 'service', 'assembly', 'assortment');
6 ALTER TABLE parts ADD COLUMN part_type part_type_enum;
8 UPDATE parts SET part_type = 'assembly' WHERE assembly IS TRUE;
9 UPDATE parts SET part_type = 'service' WHERE inventory_accno_id IS NULL and part_type IS NULL;
10 UPDATE parts SET part_type = 'part' WHERE assembly IS FALSE AND inventory_accno_id IS NOT NULL AND part_type IS NULL;
12 -- don't set a default for now to help with finding bugs where no part_type is passed
13 ALTER TABLE parts ALTER COLUMN part_type SET NOT NULL;
15 CREATE OR REPLACE FUNCTION update_purchase_price() RETURNS trigger AS '
17 if tg_op = ''DELETE'' THEN
18 UPDATE parts SET lastcost = COALESCE((select sum ((a.qty * (p.lastcost / COALESCE(pf.factor,
19 1)))) as summe from assembly a left join parts p on (p.id = a.parts_id)
20 LEFT JOIN price_factors pf on (p.price_factor_id = pf.id) where a.id = parts.id),0)
21 WHERE part_type = ''assembly'' and id = old.id;
22 return old; -- old ist eine referenz auf die geloeschte reihe
24 UPDATE parts SET lastcost = COALESCE((select sum ((a.qty * (p.lastcost / COALESCE(pf.factor,
25 1)))) as summe from assembly a left join parts p on (p.id = a.parts_id)
26 LEFT JOIN price_factors pf on (p.price_factor_id = pf.id)
27 WHERE a.id = parts.id),0) where part_type = ''assembly'' and id = new.id;
28 return new; -- entsprechend new, wird wahrscheinlich benoetigt, um den korrekten Eintrag
29 -- zu filtern bzw. dann zu aktualisieren