diff options
Diffstat (limited to 'sql')
| -rw-r--r-- | sql/mlpdb.sql | 13 |
1 files changed, 7 insertions, 6 deletions
diff --git a/sql/mlpdb.sql b/sql/mlpdb.sql index b4f0989c..5d25ac86 100644 --- a/sql/mlpdb.sql +++ b/sql/mlpdb.sql @@ -39,7 +39,7 @@ CREATE TABLE index_maturity( Index index_type,
series smallint,
tenor tenor,
- maturity date
+ maturity date,
PRIMARY KEY(index, series, tenor)
);
@@ -164,7 +164,7 @@ CREATE OR REPLACE FUNCTION nameToBasketID(index_name varchar(4), p_date date) RE p_series smallint;
p_basketid integer;
BEGIN
- p_index := left(index_name, 2)::index_type;
+ p_index := upper(left(index_name, 2))::index_type;
p_series := right(index_name, -2);
SELECT MIN(basketid) INTO p_basketid FROM index_version WHERE Index=p_index and
Series=p_series and lastdate>=p_date;
@@ -194,10 +194,11 @@ CREATE OR REPLACE FUNCTION historical_cds_quotes(date) RETURNS SETOF cds_quotes 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
- RETURN QUERY SELECT max(ticker) AS t, max(a.date), array_agg((a.runningbid+a.runningask)/2 ORDER BY tenor),
+ 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) a RIGHT JOIN
- (SELECT generate_series(1, 8) AS tenor, unnest(cds_curve) AS curve_ticker, ticker, company_id
+ (SELECT generate_series(1, 8) AS tenor, unnest(cds_curve) AS curve_ticker, markit_ticker, company_id
FROM index_members($1, $2)) b
ON b.curve_ticker = a.curve_ticker GROUP by company_id ORDER BY t;
END;
@@ -206,11 +207,11 @@ CREATE OR REPLACE FUNCTION curve_quotes(varchar(4), date) RETURNS CREATE OR REPLACE FUNCTION curve_quotes_fmt(varchar(4), date) RETURNS
TABLE(cds_ticker text, date date, spread_curve text, upfront_curve text, recovery_curve text) AS $$
BEGIN
- RETURN QUERY SELECT max(ticker) AS t, max(a.date),
+ RETURN QUERY SELECT max(markit_ticker) AS t, max(a.date),
string_agg(to_char((a.runningbid+a.runningask)/2, 'FM999'), ',' ORDER BY tenor),
string_agg(to_char((a.upfrontbid+a.upfrontask)/2, 'FM99D99'), ',' ORDER BY tenor),
string_agg(to_char(a.Recovery, 'FM0D99'), ',' ORDER BY tenor) FROM historical_cds_quotes($2) a RIGHT JOIN
- (SELECT generate_series(1, 8) AS tenor, unnest(cds_curve) AS curve_ticker, ticker, company_id
+ (SELECT generate_series(1, 8) AS tenor, unnest(cds_curve) AS curve_ticker, markit_ticker, company_id
FROM index_members($1, $2)) b
ON b.curve_ticker = a.curve_ticker GROUP by company_id ORDER BY t;
END;
|