diff options
Diffstat (limited to 'sql')
| -rw-r--r-- | sql/serenitasdb.sql | 27 |
1 files changed, 21 insertions, 6 deletions
diff --git a/sql/serenitasdb.sql b/sql/serenitasdb.sql index 69c793e5..b78179d7 100644 --- a/sql/serenitasdb.sql +++ b/sql/serenitasdb.sql @@ -80,7 +80,7 @@ CREATE TABLE index_maturity_markit( CREATE OR REPLACE VIEW risk_num_per_quote AS
SELECT a.*, b.trancheupfrontmid, b.trancherunningmid, b.indexrefprice, b.indexrefspread,
b.tranchedelta, b.quotesource
- FROM risk_numbers_new a
+ FROM risk_numbers a
JOIN tranche_quotes b ON a.tranche_id = b.id;
CREATE OR REPLACE VIEW index_desc AS
@@ -385,11 +385,12 @@ RETURNS SETOF cds_issuers_old AS $$ $$ LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION curve_quotes(varchar(4), date, bbgSource DEFAULT 'MKIT') RETURNS
- TABLE(cds_ticker text, date date, spread_curve float[], upfront_curve float[], recovery_curve float[]) AS $$
+ TABLE(cds_ticker text, date date, spread_curve float[],
+ upfront_curve float[], recovery_curve float[]) AS $$
BEGIN
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.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, $3) a RIGHT JOIN
(SELECT generate_series(1, 8) AS tenor, unnest(cds_curve) AS curve_ticker, markit_ticker, company_id
FROM index_members($1, $2)) b
@@ -540,7 +541,8 @@ $$ END;
$$ language plpgsql;
-CREATE TABLE risk_numbers(
+-- deprecated
+CREATE TABLE risk_numbers_really_old(
date date,
index index_type,
series integer,
@@ -562,7 +564,8 @@ CREATE TABLE risk_numbers( el float[],
PRIMARY KEY(date, index, series, tenor));
-CREATE TABLE risk_numbers_new(
+-- deprecated
+CREATE TABLE risk_numbers_old(
id serial PRIMARY KEY,
tranche_id integer REFERENCES tranche_quotes(id),
date date,
@@ -587,6 +590,16 @@ CREATE TABLE risk_numbers_new( GRANT ALL ON risk_numbers to serenitas_user;
+CREATE OR REPLACE VIEW risk_numbers AS
+SELECT tranche_id, quotedate as date, index, series, tenor,
+ index_price, index_basis, index_expected_loss, index_duration,
+ index_theta, attach, detach, corr_at_detach,
+ delta, fwd_delta as forward_delta, gamma, theta, corr01,
+ duration, expected_loss
+FROM tranche_risk
+JOIN tranche_quotes ON tranche_id=tranche_quotes.id;
+
+
CREATE TABLE tranche_risk(
id serial PRIMARY KEY,
tranche_id integer REFERENCES tranche_quotes(id),
@@ -607,6 +620,8 @@ CREATE TABLE tranche_risk( GRANT ALL ON tranche_risk to serenitas_user;
+ALTER TABLE tranche_risk ADD UNIQUE(tranche_id);
+
CREATE OR REPLACE FUNCTION riskmonitor_getindicesinfo2(
IN p_date date,
IN p_fromseries smallint,
|