diff options
| -rw-r--r-- | sql/et_tables.sql | 65 |
1 files changed, 49 insertions, 16 deletions
diff --git a/sql/et_tables.sql b/sql/et_tables.sql index c0970c26..3e55e2a3 100644 --- a/sql/et_tables.sql +++ b/sql/et_tables.sql @@ -168,7 +168,7 @@ CREATE TYPE bloomberg_cpntype AS ENUM('FIXED', 'FLOATING', 'EXCHANGED', 'DEFAULT 'PAY-IN-KIND', 'VARIABLE', 'ZERO COUPON', 'STEP CPN', 'FLAT TRADING', 'NONE', 'FUNGED', 'STEP', 'ZERO'); -CREATE TABLE bloomberg_corp ( +CREATE TABLE bloomberg_corp_old ( Cusip varchar(9), Price float, PricingDate date, @@ -184,9 +184,41 @@ CREATE TABLE bloomberg_corp ( secondlien boolean, defaulted boolean, Source bloomberg_source, + id_bb_unique text, + security_type text, + amount_outstanding float, PRIMARY KEY (Cusip, PricingDate) ); +CREATE TABLE bloomberg_corp_ref ( + id_bb_unique text PRIMARY KEY, + cusip varchar(9)[], + Issuer text, + Maturity date, + Coupon float, + CouponType bloomberg_cpntype, + Frequency int, + Spread float, + Libor_floor float, + tranche_size float, + covlite boolean, + secondlien boolean, + security_type text, + issue_dt date, + loanxid varchar(8)[]); + + +CREATE TABLE bloomberg_corp( + pricingdate date, + id_bb_unique text REFERENCES bloomberg_corp_ref, + Price float, + loan_margin float, + amount_outstanding float, + defaulted boolean, + Source bloomberg_source, + PRIMARY KEY (id_bb_unique, pricingdate)); + + GRANT ALL ON bloomberg_corp TO et_user; CREATE TABLE bloomberg_mtge ( @@ -413,24 +445,25 @@ CREATE OR REPLACE FUNCTION et_ReadMapped(p_issuername text, p_maturity date, p_s END; $$ LANGUAGE plpgsql; -CREATE OR REPLACE FUNCTION historical_markit_prices(p_date date) - -- DEPRACATED (use historical_markit_prices2) - RETURNS SETOF markit_prices AS $$ - BEGIN - RETURN QUERY SELECT b.* - FROM (SELECT MAX(c.pricingdate) AS latestdate, c.loanxid FROM markit_prices c - WHERE c.pricingdate<=p_date GROUP BY c.loanxid) a - JOIN markit_prices b ON a.loanxid = b.loanxid AND a.latestdate= b.pricingdate; - END; - $$ LANGUAGE plpgsql; +CREATE OR REPLACE VIEW latest_bloomberg_corp AS +SELECT b.pricingdate, unnest(c.cusip) as cusip, b.price, c.issuer, c.tranche_size, + b.amount_outstanding, c.covlite, c.secondlien, b.defaulted FROM + (SELECT MAX(pricingdate) AS pricingdate, id_bb_unique FROM bloomberg_corp + GROUP BY id_bb_unique) a + JOIN bloomberg_corp b USING (id_bb_unique, pricingdate) JOIN bloomberg_corp_ref c + USING (id_bb_unique); + +GRANT ALL ON latest_bloomberg_corp TO et_user; CREATE OR REPLACE FUNCTION historical_bloomberg_corp(p_date date) - RETURNS SETOF bloomberg_corp AS $$ + RETURNS SETOF latest_bloomberg_corp AS $$ BEGIN - RETURN QUERY SELECT b.* FROM - (SELECT MAX(c.pricingdate) AS latestdate, c.cusip FROM bloomberg_corp c - WHERE c.pricingdate<=p_date GROUP BY c.cusip) a - JOIN bloomberg_corp b ON a.cusip = b.cusip AND a.latestdate= b.pricingdate; + RETURN QUERY SELECT b.pricingdate, unnest(c.cusip) as cusip, b.price, + c.issuer, c.tranche_size, b.amount_outstanding, c.covlite, c.secondlien, b.defaulted FROM + (SELECT MAX(pricingdate) AS pricingdate, id_bb_unique FROM bloomberg_corp WHERE + pricingdate<=p_date GROUP BY id_bb_unique) a + JOIN bloomberg_corp b USING (id_bb_unique, pricingdate) JOIN bloomberg_corp_ref c + USING (id_bb_unique); END; $$ LANGUAGE plpgsql; |