diff options
Diffstat (limited to 'sql')
| -rw-r--r-- | sql/serenitasdb.sql | 17 |
1 files changed, 11 insertions, 6 deletions
diff --git a/sql/serenitasdb.sql b/sql/serenitasdb.sql index 5363fcfe..52d7b4ff 100644 --- a/sql/serenitasdb.sql +++ b/sql/serenitasdb.sql @@ -33,9 +33,12 @@ CREATE TABLE index_version( Version smallint,
IndexFactor float,
CumulativeLoss float,
+ lastdate date,
PRIMARY KEY(BasketID)
);
+GRANT ALL ON index_version TO serenitas_users;
+
CREATE TABLE index_maturity(
Index index_type,
series smallint,
@@ -137,7 +140,8 @@ CREATE TYPE DocClause AS ENUM('No Restructuring', 'Modified Modified Restructuri CREATE TYPE tenor AS ENUM('6mo', '1yr', '2yr', '3yr', '4yr', '5yr', '7yr', '10yr');
CREATE TYPE index_type AS ENUM('IG', 'HY', 'EU', 'LCDX', 'XO');
-CREATE TABLE CDS_Issuers(
+CREATE TABLE CDS_Issuers_old(
+ --DEPRECATED
Name text,
company_id integer,
ticker text,
@@ -151,7 +155,7 @@ CREATE TABLE CDS_Issuers( spread integer,
PRIMARY KEY(company_id));
-GRANT ALL ON CDS_Issuers to serenitas_user;
+GRANT ALL ON CDS_Issuers_old to serenitas_user;
CREATE TABLE bbg_issuers(
Name text,
@@ -168,7 +172,7 @@ GRANT ALL ON bbg_issuers to serenitas_user; CREATE TABLE bbg_markit_mapping(
date date,
- company_id integer REFERENCES CDS_Issuers,
+ company_id integer REFERENCES bbg_Issuers,
markit_ticker text,
markit_tier tier,
spread integer,
@@ -204,12 +208,13 @@ CREATE OR REPLACE FUNCTION nameToBasketID(index_name varchar(4), p_date date) RE $$ LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION index_members(index_name varchar(4), p_date date)
- RETURNS SETOF CDS_Issuers AS $$
+ RETURNS SETOF cds_issuers_old AS $$
DECLARE
basketid integer;
BEGIN
SELECT nameToBasketID(index_name, p_date) INTO basketid;
- RETURN QUERY SELECT * FROM CDS_Issuers WHERE index_list @> Array[basketid];
+ RETURN QUERY SELECT * FROM historical_cds_issuers(p_date)
+ WHERE index_list @> Array[basketid];
END;
$$ LANGUAGE plpgsql;
@@ -227,7 +232,7 @@ 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 $$
+CREATE OR REPLACE FUNCTION historical_cds_issuers(date) RETURNS SETOF cds_issuers_old 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
|