diff options
Diffstat (limited to 'sql')
| -rw-r--r-- | sql/serenitasdb.sql | 33 |
1 files changed, 33 insertions, 0 deletions
diff --git a/sql/serenitasdb.sql b/sql/serenitasdb.sql index 9aead259..02fa9332 100644 --- a/sql/serenitasdb.sql +++ b/sql/serenitasdb.sql @@ -152,6 +152,29 @@ CREATE TABLE CDS_Issuers( GRANT ALL ON CDS_Issuers to serenitas_user;
+CREATE TABLE bbg_issuers(
+ Name text,
+ company_id integer,
+ ticker text,
+ currency curr,
+ seniority sen,
+ doc_clause DocClause,
+ cds_curve text[8] UNIQUE,
+ index_list integer[],
+ PRIMARY KEY(company_id));
+
+GRANT ALL ON bbg_issuers to serenitas_user;
+
+CREATE TABLE bbg_markit_mapping(
+ date date,
+ company_id integer REFERENCES CDS_Issuers,
+ markit_ticker text,
+ markit_tier tier,
+ spread integer,
+ PRIMARY KEY(company_id, date));
+
+GRANT ALL ON bbg_markit_mapping to serenitas_user;
+
CREATE TABLE cds_quotes(
Date Date,
curve_ticker text,
@@ -203,6 +226,16 @@ CREATE OR REPLACE FUNCTION historical_cds_quotes(date) RETURNS SETOF cds_quotes -- END;
$$ LANGUAGE plpgsql;
+CREATE OR REPLACE FUNCTION historical_cds_issuers(date) 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);
+ END;
+ $$ LANGUAGE plpgsql;
+
CREATE OR REPLACE FUNCTION curve_quotes(varchar(4), date) RETURNS
TABLE(cds_ticker text, date date, spread_curve float[], upfront_curve float[], recovery_curve float[]) AS $$
BEGIN
|