diff options
Diffstat (limited to 'sql/et_tables.sql')
| -rw-r--r-- | sql/et_tables.sql | 31 |
1 files changed, 19 insertions, 12 deletions
diff --git a/sql/et_tables.sql b/sql/et_tables.sql index 3713d535..b039edb5 100644 --- a/sql/et_tables.sql +++ b/sql/et_tables.sql @@ -341,10 +341,11 @@ CREATE TABLE cusip_universe_old ( GRANT ALL ON cusip_universe TO et_user; CREATE TABLE cusip_ref( - Cusip varchar(9) PRIMARY KEY, + cusip_id serial PRIMARY KEY, + Cusip varchar(9) NOT NULL, ISIN varchar(12), bloomberg_ticker text, - dealname varchar(10), + dealname varchar(10) NOT NULL, tranche text, paripassu_tranches text[], Orig_Balance float, @@ -353,10 +354,11 @@ CREATE TABLE cusip_ref( Orig_Detach float, Floater_Index text, Spread float, - type text); + type text + UNIQUE (Cusip, dealname)); CREATE TABLE cusip_update( - Cusip varchar(9) REFERENCES cusip_ref, + cusip_id int REFERENCES cusip_ref, curr_balance float, factor float, coupon float, @@ -367,9 +369,13 @@ CREATE TABLE cusip_update( PRIMARY KEY(cusip, updatedate)); CREATE OR REPLACE VIEW cusip_universe AS - SELECT a.*, b.curr_balance, b.factor, b.coupon, b.curr_moody, b.curr_attach, b.curr_detach, + SELECT a.cusip, a.isin, a.bloomberg_ticker, a.dealname, a.tranche, + a.paripassu_tranches, a.orig_balance, a.orig_moody, + a.orig_attach, a.orig_detach, a.floater_index, a.spread, a.type, + b.curr_balance, b.factor, b.coupon, b.curr_moody, + b.curr_attach, b.curr_detach, b.updatedate FROM cusip_ref a - JOIN cusip_update b USING (cusip); + JOIN cusip_update b USING (cusip_id); CREATE OR REPLACE VIEW latest_clo_universe AS SELECT b.dealname, "Deal Name", Manager, b.orig_deal_bal, b."Curr Deal Bal", @@ -386,13 +392,14 @@ GRANT ALL ON latest_clo_universe TO et_user; CREATE OR REPLACE VIEW latest_cusip_universe AS WITH latest_cusip_update AS - (SELECT DISTINCT ON (cusip) * FROM cusip_update - ORDER BY cusip, updatedate DESC) - SELECT a.*, b.curr_balance, b.coupon, b.curr_moody, b.curr_attach, + (SELECT DISTINCT ON (cusip_id) * FROM cusip_update + ORDER BY cusip_id, updatedate DESC) + SELECT a.cusip, a.isin, a.bloomberg_ticker, a.dealname, + a.tranche, a.paripassu_tranches, a.orig_balance, a.orig_moody, + a.orig_attach, a.orig_detach, a.floater_index, a.spread, + a.type, b.curr_balance, b.coupon, b.curr_moody, b.curr_attach, b.curr_detach, b.updatedate FROM cusip_ref a - JOIN latest_cusip_update b USING (cusip); - -GRANT ALL ON latest_cusip_universe TO et_user; + JOIN latest_cusip_update b USING (cusip_id); CREATE OR REPLACE FUNCTION historical_clo_universe(p_dealname varchar(10), p_date date) RETURNS SETOF latest_clo_universe AS $$ |