1 files changed, 57 insertions, 1 deletions
diff --git a/sql/mlpdb.sql b/sql/mlpdb.sql
index 81380e86..4d992732 100644
--- a/sql/mlpdb.sql
+++ b/sql/mlpdb.sql
@@ -112,6 +112,62 @@ CREATE TABLE CDS_Issuers(
        seniority sen,
        doc_clause DocClause,
        cds_curve text[8],
-       index_list integer[]);
+       index_list integer[],
+       PRIMARY KEY(company_id));
 
 GRANT ALL ON  CDS_Issuers to mlpdb_user;
+
+CREATE TABLE cds_quotes(
+       Date Date,
+       curve_ticker text,
+       UpfrontBid float,
+       UpfrontAsk float,
+       RunningBid float,
+       RunningAsk float,
+       Source bbgSource,
+       Recovery float,
+       PRIMARY KEY(curve_ticker, Date));
+
+GRANT ALL ON cds_quotes to mlpdb_user;
+
+CREATE OR REPLACE FUNCTION nameToBasketID(index_name varchar(4)) RETURNS integer AS $$
+       DECLARE
+	p_index varchar(2);
+      	p_series smallint;
+       	p_basketid integer;
+       BEGIN
+	p_index := left(index_name, 2);
+       	p_series := right(index_name, -2);
+       	SELECT MAX(basketid) INTO p_basketid FROM index_desc WHERE Index=p_index and Series=p_series;
+       	RETURN p_basketid;
+       END;
+       $$ LANGUAGE plpgsql;
+
+GRANT ALL ON nameToBasketID TO mlpdb_user;
+
+CREATE OR REPLACE FUNCTION index_members(index_name varchar(4))
+       RETURNS SETOF CDS_Issuers AS $$
+       DECLARE
+	basketid integer;
+       BEGIN
+	SELECT nameToBasketID(index_name) INTO basketid;
+       	RETURN QUERY SELECT * FROM CDS_Issuers WHERE index_list @> Array[basketid];
+       END;
+       $$ LANGUAGE plpgsql;
+
+CREATE OR REPLACE FUNCTION historical_cds_quotes(date) RETURNS SETOF cds_quotes AS $$
+       RETURN QUERY SELECT b.*
+       FROM (SELECT max(c.Date) AS latestdate, c.curve_ticker FROM cds_quotes c
+       WHERE cds_quotes.Date<=p_date GROUP BY c.curve_ticker) a
+       JOIN cds_quotes b ON a.curve_ticker = b.curve_ticker AND a.latestdate=b.Date;
+       END;
+       $$ LANGUAGE plpgsql;
+
+CREATE OR REPLACE FUNCTION curve_quotes(varchar(4), date) RETURNS
+       TABLE(cds_ticker text, spread_curve float[]) AS $$
+       BEGIN
+       RETURN QUERY SELECT max(ticker) AS t, array_agg((a.runningbid+a.runningask)/2 ORDER BY tenor) FROM historical_cds_quotes($2) a JOIN
+       (SELECT generate_series(1, 8) AS tenor, unnest(cds_curve) AS curve_ticker, ticker, company_id FROM index_members($1)) b
+       ON b.curve_ticker = a.curve_ticker GROUP by company_id ORDER BY t;
+       END;
+       $$ LANGUAGE plpgsql;