diff options
| -rw-r--r-- | sql/dawn.sql | 62 |
1 files changed, 32 insertions, 30 deletions
diff --git a/sql/dawn.sql b/sql/dawn.sql index 6c9c9d18..0b1f2619 100644 --- a/sql/dawn.sql +++ b/sql/dawn.sql @@ -3,7 +3,7 @@ CREATE TYPE bond_strat AS ENUM('M_STR_MAV', 'M_STR_MEZZ', 'CSO_TRANCH', 'M_CLO_BB20', 'M_CLO_AAA', 'M_CLO_BBB', 'M_MTG_IO', 'M_MTG_THRU', 'M_MTG_GOOD', 'M_MTG_B4PR', 'M_MTG_RW', 'M_MTG_FP', 'M_MTG_LMG', 'M_MTG_SD', 'M_MTG_PR', 'M_MTG_CRT_SD', 'CRT_LD', 'CRT_LD_JNR', - 'M_MTG_REPO'); + 'MTG_REPO'); CREATE TYPE cds_strat AS ENUM('HEDGE_CSO', 'HEDGE_CLO', 'HEDGE_MAC', 'HEDGE_MBS', 'SER_IGSNR', 'SER_IGMEZ', 'SER_IGEQY', 'SER_IGINX', 'SER_HYSNR', @@ -375,37 +375,39 @@ INSERT OR UPDATE OF orig_attach, orig_detach, security_id ON cds FOR EACH ROW EXECUTE PROCEDURE update_attach(); -CREATE TABLE repo(id serial primary key, - dealid varchar(28) UNIQUE, - fund fund NOT NULL DEFAULT 'SERCGMAST', - lastupdate timestamptz DEFAULT now(), - action action NOT NULL, - folder bond_strat NOT NULL, - custodian varchar(12) NOT NULL, - cashaccount varchar(12) NOT NULL, - cp_code varchar(12) NOT NULL REFERENCES counterparties(code) ON UPDATE CASCADE, - trade_date date NOT NULL, - settle_date date NOT NULL, - cusip varchar(9), - isin varchar(12), - identifier varchar(12), - description varchar(32) NOT NULL, - transaction_indicator repo_type NOT NULL, - faceamount float NOT NULL, - price float NOT NULL, - currency currency NOT NULL, - expiration_date date, - weighted_amount float, - haircut float, - repo_rate float NOT NULL, - call_notice call_notice, - daycount day_count, - ticket text - CHECK ( (haircut is NOT NULL AND weighted_amount is NULL) OR - (haircut is NULL AND weighted_amount is NOT NULL) ); - CHECK (cusip is NOT NULL OR isin is NOT NULL) +CREATE TABLE repos ( + id int4 NOT NULL DEFAULT nextval('repos_temp_id_seq'::regclass), + dealid varchar(28) NULL GENERATED ALWAYS AS ((('SCREPO'::text || id::text))) STORED, + "fund" fund NOT NULL, + lastupdate timestamptz NULL DEFAULT now(), + "action" action NULL, + folder bond_strat NOT NULL, + custodian varchar(12) NOT NULL, + cashaccount varchar(12) NOT NULL, + cp_code varchar(12) NOT NULL, + trade_date date NOT NULL, + settle_date date NOT NULL, + cusip varchar(9) NULL, + isin varchar(12) NULL, + identifier varchar(12) NULL, + description varchar(32) NOT NULL, + transaction_indicator repo_type NULL, + faceamount float8 NOT NULL, + price float8 NOT NULL, + "currency" currency NOT NULL DEFAULT 'USD'::currency, + expiration_date date NULL, + weighted_amount float8 NULL, + haircut float8 NULL, + repo_rate float8 NOT NULL, + "call_notice" call_notice NULL, + daycount day_count NULL, + ticket varchar NULL, + open_repo bool NULL, + CONSTRAINT repos_temp_pkey PRIMARY KEY (id), + CONSTRAINT repos_temp_cp_code_fkey FOREIGN KEY (cp_code) REFERENCES public.counterparties(code) ); + CREATE TRIGGER repo_dealid AFTER INSERT ON repo FOR EACH ROW EXECUTE PROCEDURE auto_dealid(); |