diff options
Diffstat (limited to 'sql/mlpdb.sql')
| -rw-r--r-- | sql/mlpdb.sql | 30 |
1 files changed, 26 insertions, 4 deletions
diff --git a/sql/mlpdb.sql b/sql/mlpdb.sql index b0b668b9..3bf72394 100644 --- a/sql/mlpdb.sql +++ b/sql/mlpdb.sql @@ -72,8 +72,28 @@ CREATE TABLE quotes ( QuoteSource varchar(4)
);
-GRANT ALL ON quotes TO mlpdb_user;
+CREATE TABLE tranche_quotes (
+ QuoteDate timestamp,
+ Index index_type,
+ Series smallint,
+ Version smallint,
+ Tenor tenor,
+ Attach smallint,
+ Detach smallint,
+ TrancheUpfront float,
+ TrancheRunning float,
+ IndexRefPrice float,
+ IndexRefSpread float,
+ IndexDuration float,
+ TrancheDuration float,
+ TrancheDelta float,
+ CorrAtDetachment float,
+ Basis float,
+ QuoteSource varchar(4)
+);
+GRANT ALL ON quotes TO mlpdb_user;
+GRANT ALL ON tranche_quotes TO mlpdb_user;
-- Idiosyncracies:
@@ -167,11 +187,13 @@ CREATE OR REPLACE FUNCTION historical_cds_quotes(date) RETURNS SETOF cds_quotes $$ LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION curve_quotes(varchar(4), date) RETURNS
- TABLE(cds_ticker text, spread_curve float[], recovery_curve float[]) AS $$
+ TABLE(cds_ticker text, spread_curve float[], upfront_curve float[], recovery_curve float[]) AS $$
BEGIN
RETURN QUERY SELECT max(ticker) AS t, array_agg((a.runningbid+a.runningask)/2 ORDER BY tenor),
- array_agg(a.Recovery 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
+ 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
+ FROM index_members($1, $2)) b
ON b.curve_ticker = a.curve_ticker GROUP by company_id ORDER BY t;
END;
$$ LANGUAGE plpgsql;
|