2024-10-09T04:46:11.sql (1288B)
1 BEGIN; 2 3 CREATE TABLE spaceplanner.furniture_styles ( 4 type varchar REFERENCES spaceplanner.furniture_types(name) NOT NULL, 5 style varchar NOT NULL, 6 CONSTRAINT unique_style_for_type UNIQUE(style, type) 7 ); 8 9 INSERT INTO spaceplanner.furniture_styles (type, style) VALUES 10 ('table', 'round'), 11 ('table', 'semi-circle'); 12 13 ALTER TABLE spaceplanner.furniture 14 ADD COLUMN style varchar; 15 16 ALTER TABLE spaceplanner.furniture_types 17 ADD COLUMN class varchar; 18 19 INSERT INTO spaceplanner.furniture_types (name, class) VALUES 20 ('toilet', 'fixture'); 21 22 CREATE FUNCTION spaceplanner.is_style_for_type() 23 RETURNS trigger AS $is_style_for_type$ 24 DECLARE 25 bad int; 26 BEGIN 27 SELECT count(style) 28 INTO bad 29 FROM spaceplanner.furniture_styles 30 WHERE type = NEW.type AND style = NEW.style 31 LIMIT 1; 32 IF (NEW.style <> null AND bad <> 1) THEN 33 RAISE EXCEPTION 'Style (%s) does not exist for that type (%s)', NEW.style, NEW.type; 34 END IF; 35 RETURN NEW; 36 END; 37 $is_style_for_type$ LANGUAGE plpgsql; 38 39 CREATE CONSTRAINT TRIGGER check_furniture_style 40 AFTER INSERT OR UPDATE ON spaceplanner.furniture 41 FOR EACH ROW 42 EXECUTE FUNCTION spaceplanner.is_style_for_type(); 43 44 END;