diff options
Diffstat (limited to 'sql')
| -rw-r--r-- | sql/serenitasdb.sql | 20 |
1 files changed, 12 insertions, 8 deletions
diff --git a/sql/serenitasdb.sql b/sql/serenitasdb.sql index 5b6f6990..90decbe0 100644 --- a/sql/serenitasdb.sql +++ b/sql/serenitasdb.sql @@ -318,12 +318,14 @@ CREATE OR REPLACE FUNCTION index_members(index_name varchar(4), p_date date) END;
$$ LANGUAGE plpgsql;
-CREATE OR REPLACE FUNCTION historical_cds_quotes(date) RETURNS SETOF cds_quotes AS $$
+
+CREATE OR REPLACE FUNCTION historical_cds_quotes(date, bbgSource) RETURNS SETOF cds_quotes AS $$
BEGIN
RETURN QUERY SELECT b.*
FROM (SELECT max(c.Date) AS latestdate, c.curve_ticker FROM cds_quotes c
- WHERE c.Date BETWEEN $1 - interval '1 month' AND $1 GROUP BY c.curve_ticker) a
- JOIN cds_quotes b ON a.curve_ticker = b.curve_ticker AND a.latestdate=b.Date;
+ WHERE (c.Date BETWEEN $1 - interval '1 month' AND $1) AND source=$2 GROUP BY c.curve_ticker) a
+ JOIN cds_quotes b ON a.curve_ticker = b.curve_ticker AND a.latestdate=b.Date
+ WHERE source=$2;
END;
-- simpler query but slower
-- BEGIN
@@ -332,6 +334,7 @@ CREATE OR REPLACE FUNCTION historical_cds_quotes(date) RETURNS SETOF cds_quotes -- END;
$$ LANGUAGE plpgsql;
+
CREATE OR REPLACE FUNCTION historical_cds_issuers(date DEFAULT current_date)
RETURNS SETOF cds_issuers_old AS $$
BEGIN
@@ -343,27 +346,28 @@ RETURNS SETOF cds_issuers_old AS $$ END;
$$ LANGUAGE plpgsql;
-CREATE OR REPLACE FUNCTION curve_quotes(varchar(4), date) RETURNS
+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 $$
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.Recovery ORDER BY tenor) FROM historical_cds_quotes($2) a RIGHT JOIN
+ 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
ON b.curve_ticker = a.curve_ticker GROUP by company_id ORDER BY t;
END;
$$ LANGUAGE plpgsql;
-CREATE OR REPLACE FUNCTION curve_quotes_fmt(varchar(4), date) RETURNS
+CREATE OR REPLACE FUNCTION curve_quotes_fmt(varchar(4), date, bbgSource DEFAULT 'MKIT') RETURNS
TABLE(cds_ticker text, date date, spread_curve text, upfront_curve text, recovery_curve text) AS $$
BEGIN
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, markit_ticker, company_id
+ string_agg(to_char(a.Recovery, 'FM0D99'), ',' 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
ON b.curve_ticker = a.curve_ticker GROUP by company_id ORDER BY t;
END;
|