diff options
Diffstat (limited to 'sql/serenitasdb.sql')
| -rw-r--r-- | sql/serenitasdb.sql | 69 |
1 files changed, 51 insertions, 18 deletions
diff --git a/sql/serenitasdb.sql b/sql/serenitasdb.sql index 79b11fd8..0e591d1d 100644 --- a/sql/serenitasdb.sql +++ b/sql/serenitasdb.sql @@ -258,6 +258,22 @@ CREATE TABLE CDS_Issuers_old( GRANT ALL ON CDS_Issuers_old to serenitas_user;
+CREATE TYPE cds_issuers AS
+ Name text,
+ company_id integer,
+ ticker text,
+ currency curr,
+ seniority sen,
+ doc_clause DocClause,
+ cds_curve text[8] UNIQUE,
+ index_list integer[],
+ markit_ticker text,
+ markit_tier tier,
+ spread integer,
+ event_date date);
+
+GRANT ALL ON cds_Issuers to serenitas_user;
+
CREATE TABLE bbg_issuers(
Name text,
company_id integer,
@@ -346,7 +362,7 @@ CREATE OR REPLACE FUNCTION index_redcode(p_index index_type, p_series smallint, $$ LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION index_members(index_name varchar(4), p_date date)
- RETURNS SETOF cds_issuers_old AS $$
+ RETURNS SETOF cds_issuers AS $$
DECLARE
basketid integer;
BEGIN
@@ -354,7 +370,7 @@ CREATE OR REPLACE FUNCTION index_members(index_name varchar(4), p_date date) RETURN QUERY SELECT * FROM historical_cds_issuers(p_date)
WHERE index_list @> Array[basketid];
END;
- $$ LANGUAGE plpgsql;
+$$ LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION historical_cds_quotes(date, bbgSource) RETURNS SETOF cds_quotes AS $$
@@ -374,13 +390,22 @@ CREATE OR REPLACE FUNCTION historical_cds_quotes(date, bbgSource) RETURNS SETOF CREATE OR REPLACE FUNCTION historical_cds_issuers(date DEFAULT current_date)
-RETURNS SETOF cds_issuers_old AS $$
+RETURNS SETOF cds_issuers AS $$
BEGIN
- RETURN QUERY SELECT bbg_issuers.*, map.markit_ticker, map.markit_tier, map.spread FROM
- (SELECT b.* FROM (SELECT min(c.date) AS latestdate, c.company_id FROM bbg_markit_mapping c
- WHERE c.date>=$1 GROUP BY c.company_id) a
- JOIN bbg_markit_mapping b ON a.company_id = b.company_id AND a.latestdate=b.date) map
- JOIN bbg_issuers USING (company_id);
+ RETURN QUERY SELECT bbg_issuers.*,
+ map.markit_ticker,
+ map.markit_tier,
+ map.spread,
+ event_date
+ FROM
+ (SELECT b.*
+ FROM (SELECT min(c.date) AS latestdate, c.company_id
+ FROM bbg_markit_mapping c
+ WHERE c.date>=$1 GROUP BY c.company_id) a
+ JOIN bbg_markit_mapping b
+ ON a.company_id = b.company_id AND a.latestdate=b.date) map
+ JOIN bbg_issuers USING (company_id)
+ LEFT JOIN defaulted ON map.company_id = id;
END;
$$ LANGUAGE plpgsql;
@@ -402,21 +427,24 @@ CREATE OR REPLACE FUNCTION curve_quotes2( varchar(4),
date,
float[] DEFAULT '{0.5, 1, 2, 3, 4, 5, 7, 10}'::float[],
- bbgSource DEFAULT 'MKIT') RETURNS
+ bbgsource DEFAULT 'MKIT'::bbgsource) RETURNS
TABLE(cds_ticker text, date date, spread_curve float[],
- upfront_curve float[], recovery_curve float[]) AS $$
+ upfront_curve float[], recovery_curve float[], event_date date) AS $$
BEGIN
RETURN QUERY SELECT max(markit_ticker) AS t, max(a.date),
array_agg((a.runningbid + a.runningask)/2 ORDER BY tenor),
array_agg((a.upfrontbid + a.upfrontask)/2 ORDER BY tenor),
- array_agg(a.Recovery ORDER BY tenor) FROM historical_cds_quotes($2, $4) a RIGHT JOIN
- (SELECT curve_ticker, markit_ticker, company_id FROM
- (SELECT unnest('{0.5, 1, 2, 3, 4, 5, 7, 10}'::float[]) AS tenor,
- unnest(cds_curve) AS curve_ticker,
- markit_ticker,
- company_id
- FROM index_members($1, $2)) a
- WHERE tenor=Any($3)) b
+ array_agg(a.Recovery ORDER BY tenor),
+ max(b.event_date) FROM historical_cds_quotes($2, $4) a
+ RIGHT JOIN
+ (SELECT curve_ticker, markit_ticker, company_id, c.event_date, tenor FROM
+ (SELECT unnest('{0.5, 1, 2, 3, 4, 5, 7, 10}'::float[]) AS tenor,
+ unnest(cds_curve) AS curve_ticker,
+ markit_ticker,
+ company_id,
+ d.event_date
+ FROM index_members($1, $2) d) c
+ WHERE tenor=Any($3)) b
ON b.curve_ticker = a.curve_ticker GROUP by company_id ORDER BY t;
END;
$$ LANGUAGE plpgsql;
@@ -530,6 +558,11 @@ $$ WHERE index=$1::index_type AND series=$2 AND date(timezone(''localtime'', quotedate))=$3
AND tenor =$4::tenor ORDER BY quotedate desc'
USING pg_index_type, pg_series, pg_date, pg_tenor LOOP
+ -- The JPM quotes are untriggered whereas Citi quotes are triggered
+ IF pg_index_type = 'XO' AND pg_series = 22 AND
+ r.quotesource = 'JPM' AND pg_date > '2016-04-25' THEN
+ CONTINUE;
+ END IF;
IF pg_index_type = 'HY' AND pg_series >=15 THEN
EXECUTE
'SELECT array_agg(attach ORDER BY attach ASC)||100::smallint = $6||array_agg(detach ORDER BY detach ASC)
|