diff options
Diffstat (limited to 'sql/et_tables.sql')
| -rw-r--r-- | sql/et_tables.sql | 46 |
1 files changed, 37 insertions, 9 deletions
diff --git a/sql/et_tables.sql b/sql/et_tables.sql index 3e55e2a3..25d657a2 100644 --- a/sql/et_tables.sql +++ b/sql/et_tables.sql @@ -168,6 +168,13 @@ 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 TYPE bloomberg_ln_status AS ENUM('ACCELERATED DEFAULT', 'ADMINISTRATION', 'DEFAULTED', + 'EVENT OF DEFAULT', 'FUNGED', 'GENL SYNDICATION', 'IN BANKRUPTCY', 'MANDATE', 'MATURED', + 'REFINANCED', 'REPLACED', 'RESTRUCTURED', 'RESTRUCTURED - EXCH', + 'RESTRUCTURED - FULL', 'RESTRUCTURED - STLMT', 'RETIRED', 'RETIRED DEFAULT', + 'SENIOR SYNDICATION', 'SIGNED', 'SIGNED NOT EFFECTIVE', + 'SIGNED/SYNDICATION', 'SYNDICATION CLOSED', 'WITHDRAWN'); + CREATE TABLE bloomberg_corp_old ( Cusip varchar(9), Price float, @@ -192,7 +199,7 @@ CREATE TABLE bloomberg_corp_old ( CREATE TABLE bloomberg_corp_ref ( id_bb_unique text PRIMARY KEY, - cusip varchar(9)[], + cusip varchar(9), Issuer text, Maturity date, Coupon float, @@ -200,21 +207,26 @@ CREATE TABLE bloomberg_corp_ref ( Frequency int, Spread float, Libor_floor float, - tranche_size float, + issue_size float, covlite boolean, secondlien boolean, security_type text, - issue_dt date, + issue_date date, + defaulted boolean, + default_date date, + called boolean, + called_date date, + status bloomberg_ln_status, loanxid varchar(8)[]); +GRANT ALL ON bloomberg_corp_ref TO et_user; CREATE TABLE bloomberg_corp( - pricingdate date, id_bb_unique text REFERENCES bloomberg_corp_ref, + pricingdate date, Price float, loan_margin float, amount_outstanding float, - defaulted boolean, Source bloomberg_source, PRIMARY KEY (id_bb_unique, pricingdate)); @@ -446,8 +458,11 @@ CREATE OR REPLACE FUNCTION et_ReadMapped(p_issuername text, p_maturity date, p_s $$ 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 b.pricingdate, CASE + WHEN c.cusip is NULL THEN substring(id_bb_unique from 3)::varchar(9) + ELSE c.cusip + END, b.price, c.issuer, c.issue_size, + b.amount_outstanding, c.covlite, c.secondlien, c.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 @@ -455,11 +470,24 @@ SELECT b.pricingdate, unnest(c.cusip) as cusip, b.price, c.issuer, c.tranche_siz GRANT ALL ON latest_bloomberg_corp TO et_user; +CREATE OR REPLACE VIEW latest_bloomberg_corp2 AS +SELECT b.pricingdate, id_bb_unique, b.price, b.loan_margin, b.source, c.issuer, c.issue_size, + b.amount_outstanding, c.covlite, c.secondlien, c.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_corp2 TO et_user; + CREATE OR REPLACE FUNCTION historical_bloomberg_corp(p_date date) RETURNS SETOF latest_bloomberg_corp AS $$ BEGIN - 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 + RETURN QUERY SELECT b.pricingdate, CASE + WHEN c.cusip is NULL THEN substring(id_bb_unique from 3)::varchar(9) + ELSE c.cusip + END, b.price, + c.issuer, c.issue_size, b.amount_outstanding, c.covlite, c.secondlien, c.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 |