1 -- Evergreen DB patch 0546.schema.sip_statcats.sql
5 -- check whether patch can be applied
6 SELECT evergreen.upgrade_deps_block_check('0546', :eg_version);
8 CREATE TABLE actor.stat_cat_sip_fields (
9 field CHAR(2) PRIMARY KEY,
11 one_only BOOL NOT NULL DEFAULT FALSE
13 COMMENT ON TABLE actor.stat_cat_sip_fields IS $$
14 Actor Statistical Category SIP Fields
16 Contains the list of valid SIP Field identifiers for
17 Statistical Categories.
19 ALTER TABLE actor.stat_cat
20 ADD COLUMN sip_field CHAR(2) REFERENCES actor.stat_cat_sip_fields(field) ON UPDATE CASCADE ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
21 ADD COLUMN sip_format TEXT;
23 CREATE FUNCTION actor.stat_cat_check() RETURNS trigger AS $func$
25 sipfield actor.stat_cat_sip_fields%ROWTYPE;
28 IF NEW.sip_field IS NOT NULL THEN
29 SELECT INTO sipfield * FROM actor.stat_cat_sip_fields WHERE field = NEW.sip_field;
30 IF sipfield.one_only THEN
31 SELECT INTO use_count count(id) FROM actor.stat_cat WHERE sip_field = NEW.sip_field AND id != NEW.id;
33 RAISE EXCEPTION 'Sip field cannot be used twice';
39 $func$ LANGUAGE PLPGSQL;
41 CREATE TRIGGER actor_stat_cat_sip_update_trigger
42 BEFORE INSERT OR UPDATE ON actor.stat_cat FOR EACH ROW
43 EXECUTE PROCEDURE actor.stat_cat_check();
45 CREATE TABLE asset.stat_cat_sip_fields (
46 field CHAR(2) PRIMARY KEY,
48 one_only BOOL NOT NULL DEFAULT FALSE
50 COMMENT ON TABLE asset.stat_cat_sip_fields IS $$
51 Asset Statistical Category SIP Fields
53 Contains the list of valid SIP Field identifiers for
54 Statistical Categories.
57 ALTER TABLE asset.stat_cat
58 ADD COLUMN sip_field CHAR(2) REFERENCES asset.stat_cat_sip_fields(field) ON UPDATE CASCADE ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
59 ADD COLUMN sip_format TEXT;
61 CREATE FUNCTION asset.stat_cat_check() RETURNS trigger AS $func$
63 sipfield asset.stat_cat_sip_fields%ROWTYPE;
66 IF NEW.sip_field IS NOT NULL THEN
67 SELECT INTO sipfield * FROM asset.stat_cat_sip_fields WHERE field = NEW.sip_field;
68 IF sipfield.one_only THEN
69 SELECT INTO use_count count(id) FROM asset.stat_cat WHERE sip_field = NEW.sip_field AND id != NEW.id;
71 RAISE EXCEPTION 'Sip field cannot be used twice';
77 $func$ LANGUAGE PLPGSQL;
79 CREATE TRIGGER asset_stat_cat_sip_update_trigger
80 BEFORE INSERT OR UPDATE ON asset.stat_cat FOR EACH ROW
81 EXECUTE PROCEDURE asset.stat_cat_check();