1 CREATE TABLE actor.stat_cat_sip_fields (
2 field CHAR(2) PRIMARY KEY,
4 one_only BOOL NOT NULL DEFAULT FALSE
6 COMMENT ON TABLE actor.stat_cat_sip_fields IS $$
7 Actor Statistical Category SIP Fields
9 Contains the list of valid SIP Field identifiers for
10 Statistical Categories.
12 ALTER TABLE actor.stat_cat
13 ADD COLUMN sip_field CHAR(2) REFERENCES actor.stat_cat_sip_fields(field) ON UPDATE CASCADE ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
14 ADD COLUMN sip_format TEXT;
16 CREATE FUNCTION actor.stat_cat_check() RETURNS trigger AS $func$
18 sipfield actor.stat_cat_sip_fields%ROWTYPE;
21 IF NEW.sip_field IS NOT NULL THEN
22 SELECT INTO sipfield * FROM actor.stat_cat_sip_fields WHERE field = NEW.sip_field;
23 IF sipfield.one_only THEN
24 SELECT INTO use_count count(id) FROM actor.stat_cat WHERE sip_field = NEW.sip_field AND id != NEW.id;
26 RAISE EXCEPTION 'Sip field cannot be used twice';
32 $func$ LANGUAGE PLPGSQL;
34 CREATE TRIGGER actor_stat_cat_sip_update_trigger
35 BEFORE INSERT OR UPDATE ON actor.stat_cat FOR EACH ROW
36 EXECUTE PROCEDURE actor.stat_cat_check();
38 CREATE TABLE asset.stat_cat_sip_fields (
39 field CHAR(2) PRIMARY KEY,
41 one_only BOOL NOT NULL DEFAULT FALSE
43 COMMENT ON TABLE asset.stat_cat_sip_fields IS $$
44 Asset Statistical Category SIP Fields
46 Contains the list of valid SIP Field identifiers for
47 Statistical Categories.
50 ALTER TABLE asset.stat_cat
51 ADD COLUMN sip_field CHAR(2) REFERENCES asset.stat_cat_sip_fields(field) ON UPDATE CASCADE ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
52 ADD COLUMN sip_format TEXT;
54 CREATE FUNCTION asset.stat_cat_check() RETURNS trigger AS $func$
56 sipfield asset.stat_cat_sip_fields%ROWTYPE;
59 IF NEW.sip_field IS NOT NULL THEN
60 SELECT INTO sipfield * FROM asset.stat_cat_sip_fields WHERE field = NEW.sip_field;
61 IF sipfield.one_only THEN
62 SELECT INTO use_count count(id) FROM asset.stat_cat WHERE sip_field = NEW.sip_field AND id != NEW.id;
64 RAISE EXCEPTION 'Sip field cannot be used twice';
70 $func$ LANGUAGE PLPGSQL;
72 CREATE TRIGGER asset_stat_cat_sip_update_trigger
73 BEFORE INSERT OR UPDATE ON asset.stat_cat FOR EACH ROW
74 EXECUTE PROCEDURE asset.stat_cat_check();