diff options
Diffstat (limited to 'sql')
| -rw-r--r-- | sql/et_tables.sql | 40 |
1 files changed, 9 insertions, 31 deletions
diff --git a/sql/et_tables.sql b/sql/et_tables.sql index af13c782..ffbed424 100644 --- a/sql/et_tables.sql +++ b/sql/et_tables.sql @@ -334,14 +334,7 @@ AS $$ $$ LANGUAGE plpythonu; CREATE OR REPLACE FUNCTION et_historicaldealinfo (p_dealname varchar(10), p_date date) - RETURNS TABLE(dealname varchar(8), updatedate date, name text, issuername text, - currentbalance float, maturity date, assetsubtype varchar(10), assettype varchar(25), - grosscoupon float, spread float, frequency varchar(1), nextpaydate date, - secondlien boolean, loanxid varchar(10), et_loanxid varchar(10), cusip varchar(9), - et_cusip varchar(10), intexprice float, intexpricesource text, intexpricedate date, - userprice float, userpricedate date, fixedorfloat intex_fixedorfloat, - defaultedflag boolean, covlite text, iscdo boolean, liborfloor float, - reinvflag boolean, currency varchar(3)) AS $$ + RETURNS SETOF et_collateral AS $$ DECLARE latestdate date; BEGIN SELECT max(et_collateral.updatedate) INTO latestdate FROM et_collateral @@ -353,14 +346,7 @@ CREATE OR REPLACE FUNCTION et_historicaldealinfo (p_dealname varchar(10), p_date $$ LANGUAGE plpgsql; CREATE OR REPLACE FUNCTION et_latestdealinfo (p_dealname varchar(10)) - RETURNS TABLE(dealname varchar(8), updatedate date, name text, issuername text, - currentbalance float, maturity date, assetsubtype varchar(10), assettype varchar(25), - grosscoupon float, spread float, frequency varchar(1), nextpaydate date, - secondlien boolean, loanxid varchar(10), et_loanxid varchar(10), cusip varchar(9), - et_cusip varchar(10), intexprice float, intexpricesource text, intexpricedate date, - userprice float, userpricedate date, fixedorfloat intex_fixedorfloat, - defaultedflag boolean, covlite text, iscdo boolean, liborfloor float, - reinvflag boolean, currency varchar(3)) AS $$ + RETURNS SETOF et_collateral AS $$ DECLARE latestdate date; BEGIN SELECT max(et_collateral.updatedate) INTO latestdate FROM et_collateral @@ -401,9 +387,7 @@ CREATE OR REPLACE FUNCTION et_ReadMapped(p_issuername text, p_maturity date, p_s -- historical_markit_prices id deprecated (use historical_markit_prices2) CREATE OR REPLACE FUNCTION historical_markit_prices(p_date date) - RETURNS TABLE(loanxid varchar(8), issuer text, dealname text, facility text, - industry text, sp varchar(4), moodys varchar(4), amount float, maturity date, - spread float, bid float, offer float, depth integer, stm float, pricingdate date) AS $$ + RETURNS SETOF markit_prices AS $$ BEGIN RETURN QUERY SELECT b.* FROM (SELECT MAX(c.pricingdate) AS latestdate, c.loanxid FROM markit_prices c @@ -413,20 +397,14 @@ CREATE OR REPLACE FUNCTION historical_markit_prices(p_date date) $$ LANGUAGE plpgsql; CREATE OR REPLACE FUNCTION historical_bloomberg_corp(p_date date) - RETURNS TABLE(cusip character varying, price double precision, pricingdate date, - issuer text, maturity date, coupon double precision, coupontype bloomberg_cpntype, - frequency integer, spread double precision, libor_floor double precision, - loan_margin double precision, covlite boolean, secondlien boolean, - defaulted boolean, source bloomberg_source) AS -$BODY$ + RETURNS SETOF 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.* 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; END; - $BODY$ - LANGUAGE plpgsql; + $$ LANGUAGE plpgsql; -- CREATE OR REPLACE FUNCTION historical_bloomberg_mtge(p_date date) -- RETURNS TABLE(CUSIP varchar(9), updatedate date, issuer text, maturity date, |